Simplicty and flexibility!


Listing lowest and highest values only.


Started by Bolt-on-Trailers
Search
You will need to Sign In to be able to add or comment on the forum!

Listing lowest and highest values only.

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.


Written by Bolt-on-Trailers 13/03/18 at 16:49:50 Dataease [{8}]FIVE

Re:Listing lowest and highest values only.

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


Written by DataEase 13/03/18 at 19:34:07 Dataease [{8}]FIVE

Re:Re:Listing lowest and highest values only.

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?


Written by Bolt-on-Trailers 13/03/18 at 21:17:54 Dataease [{8}]FIVE

Re:Re:Re:Listing lowest and highest values only.

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" .




Written by DataEase 14/03/18 at 08:47:05 Dataease [{8}]FIVE

Re:Re:Re:Re:Listing lowest and highest values only.

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?


Written by Marco Marchesi 28/03/18 at 15:13:31 Dataease [{8}]FIVE

Re:Re:Re:Re:Re:Listing lowest and highest values only.

This is for highest and lowest.


Written by DataEase 29/03/18 at 10:12:00 Dataease [{8}]FIVE