selecting the record from a group, with the lowest value.

[ZpoonZ]

i want to write a query that returns the record with the lowest value in it's group.
so if i had the following records.

color, value, time, name
orange, 1, day, bill
orange, 24, day, trevor
orange, 3, day, sally
orange, 2, night, sally
orange, 4, night, bill

so grouped by time and color, i'd get two results (bill/1, and sally/2)
orange, day, bill
orange, night, sally

i'm not sure of the easiest way to do this. is there an easiest way (ie. within one query)? or would i need to do a few queries and create look-ups etc. ?

any help would be appreciated!

 

[GumbyD]

If you GroupBy color, GroupBy Time, Min Value. Then in a new query bring in original data and the results of the above and do join and then add the name field you should get what you want. You could create a triple join between the tables to insure that you only get the minimum records.

GumbyD

i want to write a query that returns the record with the lowest value in it's group.
so if i had the following records.

color, value, time, name
orange, 1, day, bill
orange, 24, day, trevor
orange, 3, day, sally
orange, 2, night, sally
orange, 4, night, bill

so grouped by time and color, i'd get two results (bill/1, and sally/2)
orange, day, bill
orange, night, sally

i'm not sure of the easiest way to do this. is there an easiest way (ie. within one query)? or would i need to do a few queries and create look-ups etc. ?

any help would be appreciated!

 

[ZpoonZ]

thanks, i got it.

i was just wondering if there was any way of doing it in one!

thanks again.

 

[ZpoonZ]

phew. i'm still on this problem!

OK, so what if i have two records with the same value?

color, value, time, name
orange, 1, day, bill
orange, 24, day, trevor
orange, 1, day, sally
orange, 2, night, sally
orange, 4, night, bill

so for the first choice (day), i want either bill or sally, then second place the other.
i.e. either
color, value, time, name
orange, 1, day, sally
orange, 1, day, bill
orange, 24, day, trevor

or
color, value, time, name
orange, 1, day, bill
orange, 1, day, sally
orange, 24, day, trevor

i'm coming up with it, by adding another field (place)

so first query gives
orange, 1, day
as minimum

second query links this back to the original information (on color, value and time), to find out the name and then appends the records to another table with the text 'first', in the added column (place).
which returns this:
color, value, time, name, place
orange, 1, day, bill, first
orange, 1, day, sally, first

the trouble with this, is that when i come to select the second place, by chosing anything from the original list that doesn't appear in the new table (that the above was appended to) it will skip to the 3rd higest value.

so the output is then
color, value, time, name, place
orange, 1, day, bill, first
orange, 1, day, sally, first
orange, 24, day, trevor, second

finally when i crosstab (selecting the min of name, to give the first alphabetical name, and filtering on day), i get
--------first--second
orange--bill---trevor

and the REAL second place get's lost.

a long winded description, i know. sorry!
basically, i'm not interested in who is first, and who is second (example, first based on alphabetical order, is fine); just that which ever one gets first the other record with the same value is then made second.

help me obi wan, you're my only hope!