当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - How to apply DISTINCT sql query on some of fields and return all columns?

问题描述:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: