I have 6 columns and I want to select data from them while fixing one column as distinct
x
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);