Select Distinct but only on 1 returned field? 1

[Crowley16]

Hi...

what I would like to do is to have a select distinct query, but instead of returning distinct records on all fields, I would like to limit the distinct condition to just 1 field...

i.e.

SELECT DISTINCT(field1), field2, field3 FROM table;

is this possible?

--------------------
Procrastinate Now!

 

[lupins46]

So if you have :
field1 field2 field3
aa 12345 333
aa 34567 444
aa 45678 555

how do you know which values of field2 and field3 to pick?

 

[PHV]

You may try either this:
SELECT field1, Min(field2), Min(field3) FROM table GROUP BY field1;
Or this:
SELECT field1, Max(field2), Max(field3) FROM table GROUP BY field1;
Or with any variation of the aggregate functions depending of your needs.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Crowley16]

it doesn't really matter what's in the other fields, as long as there's something there...

--------------------
Procrastinate Now!

 

[Crowley16]

for some reason, when I add in the group by clause I have to do, group by field1, field2, field3...
otherwise it gives me an error saying "does not include expression field2 as part of an aggregate function"

--------------------
Procrastinate Now!

 

[Crowley16]

ahh, I see you have to use Min on every non-grouped field...

however that means the row integrity won't be kept... Any way of maintaining the row integrity?

i.e.

SELECT field1, Min(field2), field3 FROM table GROUP BY field1;


--------------------
Procrastinate Now!

 

[lupins46]

it doesn't really matter what's in the other fields, as long as there's something there...

 

[Crowley16]

well, it needs to be data from the same record...

--------------------
Procrastinate Now!

 

[PHV]

SELECT field1, First(field2), First(field3) FROM table GROUP BY field1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Crowley16]

cool...

that works great...

have a shiny star...

--------------------
Procrastinate Now!