Hi All,

I was wondering if there is a way to list the two records with the lowest and highest values only? For example let’s say I had a table of various tire companies in the US and I want to find the two companies in the state of New York that have the lowest number of employees and the highest number of employees. I could always list the employee count in order but I would rather not scroll through all the records just to see the first and last records. Any suggestion? Your help would be greatly appreciated.

Plenty of ways to do this but we can do it the "hard core" way ;-)

define "status" text .

status := "Lowest" .

for SomeData with Value = highest of somedata value or value = lowest of somedata value ;

list records

CustomerName ;

Status ;

Value in order .

status := "Highest" .

It is amazing what kind of nesting DataEase can handle. I've seen DQL's that I was sure was "idiotic" and could never work that not only works but are relatively quick to execute too.

Never underestimate the power of DQL ;-)

Result

This is exactly what I was looking for. Thank you very much. Could we take it a step further though? Say there was another field for Gender and I wanted to only select all male customers?

Simples..

define "status" text .

status := "Lowest" .

for SomeData with Value = highest of somedata with gender= male value or value = lowest of somedata with gender= male value ;

list records

CustomerName ;

Status ;

Gender ;

Value in order .

status := "Highest" .

Good solution for only the highest value, but if I need to get the first 100 customers with the highest or lowest amount how can I do?

This is for highest and lowest.