I have 6 columns and I want to select data from them while fixing one column as distinct
Column1 |Column2 |Column3 |Column4 |Column5 |Column6 --------|----------------|--------------|------------|------------|------- A |267 |2364 |001 |345.000000 |22 B |267 |2364 |001 |700.000000 |22 C |267 |2364 |001 |345.000000 |22 D |267 |2364 |001 |1200.000000 |22 E |267 |2364 |001 |1350.000000 |22 F |267 |2364 |001 |345.000000 |22 A |267 |2364 |002 |270.000000 |22 B |267 |2364 |002 |2300.000000 |22 C |267 |2364 |002 |270.000000 |22
the Display should be the following:
Column1 |Column2 |Column3 |Column4 |Column5 |Column6 --------|----------------|--------------|------------|--------------|------- A |267.000000 |2364.000000 |001 |345.000000 |22 B |267.000000 |2364.000000 |001 |700.000000 |22 C |267.000000 |2364.000000 |001 |345.000000 |22 D |267.000000 |2364.000000 |001 |1200.000000 |22 E |267.000000 |2364.000000 |001 |1350.000000 |22 F |267.000000 |2364.000000 |001 |345.000000 |22
Advertisement
Answer
Using ROW_NUMBER and TOP:
SELECT TOP 1 WITH TIES Column1, Column2, Column3, Column4, Column5, Column6 FROM yourTable ORDER BY ROW_NUMBER() OVER (PARTITION BY Column1, Column2, Column3 ORDER BY Column4);
But this assumes that the second and third column are part of what defines a “group” in your expected result set. If instead only Column1 decides that, then use this version:
SELECT TOP 1 WITH TIES Column1, Column2, Column3, Column4, Column5, Column6 FROM yourTable ORDER BY ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY Column4);