DISTINCT on some things, not on others

[jimineep]

Is it possible to use SELECT DISTINCT to use distinct on certain columns, but not on others, for example

"SELECT DISTINCT columna, columnb, columnc FROM table" would bring back all the results where columna, columnb and columnc were not all the same, and if they were it would bring back only one (the first time it happened) result.

However I only want to discriminate by columna and columnb. ie, return all the results where colmna and column are not the same, regardless of columnc, and only return one result in total for all the results where columna and b are the same.

Hope that made sense, was a little wordy!

 

[PHV]

SELECT columna, columnb, MIN(columnc)
FROM table
GROUP BY columna, columnb

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jimineep]

Still doesnt like it, think its an oracle thing

SELECT DISTINCT col1,col2, MIN(col3)
*

ERROR at line 1:
ORA-00937: not a single-group group function

 

[r937]

perhaps you might like to try your question in the Oracle forum? this is the ANSI SQL forum

by the way, if you use MIN() and any non-aggregate in the SELECT, then you must place those non-aggregates into the GROUP BY clause

r937.com | rudy.ca

 

[JarlH]

Hey, jimineep, did you remove the DISTINCT keyword when adding GROUP BY?