How i could achieve distinct for some of fields only and return result set include all columns ?
below is sql query:
SELECT DISTINCT FIELD1,FIELD2,FIELD3,FIELD4,FIELD5
RESULT COUNT - 5000
Observation - it is distinct for all five fields as DISTINCT APPLIED ON WHOLE RECORD.
BUt, FIELD1, FIELD2,FIELD43 are just duplicated and vary only FIELD 4, AND field5
Another sql query (Just skipped last two columns).
SELECT DISTINCT FIELD1,FIELD2,FIELD3 --SKIPPED FIELD4,FIELD5
RESULT COUNT - 3000
OBVESERVATION - it shows only distinct record for all three field.
PROBLEM: i need distinct record for three fields only (FIELD1,FIELD2,FIELD3) and it should include - FIELD4,FIELD5
How it could achieve.
What do you want to be returned for FIELD4 and FIELD5, the data of any of the rows with the same FIELD1-3?
select * from ( SELECT FIELD1,FIELD2,FIELD3,FIELD4,FIELD5, ROW_NUMBER() OVER (PARTITION BY FIELD1,FIELD2,FIELD3 ORDER BY FIELD4) as rn FROM T1 ) as dt where rn = 1;
Or just any data from any row?
SELECT FIELD1,FIELD2,FIELD3,MIN(FIELD4),MIN(FIELD5) FROM T1 GROUP BY FIELD1,FIELD2,FIELD3
But this might return MINs from two different rows, so no actually existing row.