问题描述:

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

FROM T1

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

FROM T1

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.

Thank You

网友答案:

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.

相关阅读:
Top