CODEWITHSUNDEEP
sqlsql-servert-sql
Andrew
Andrew

Reputation: 24846

How to select distinct rows with a specified condition

Suppose there is a table

_ _
a 1
a 2
b 2
c 3
c 4
c 1
d 2
e 5
e 6

How can I select distinct minimum value of all the rows of each group?

So the expected result here is:

_ _
a 1
b 2
c 1
d 2
e 5

EDIT

My actual table contains more columns and I want to select them all. The rows differ only in the last column (the second one in the example). I'm new to SQL and possibly my question is ill-formed in it initial view.

The actual schema is:

| day | currency ('EUR', 'USD') | diff (integer) | id (foreign key) |

The are duplicate pairs (day, currency) that differ by (diff, id). I want to see a table with uniquer pairs (day, currency) with a minimum diff from the original table.

Thanks!

Upvotes: 2

Views: 16538

Answers (4)

roman
roman

Reputation: 117380

in your case it's as simple as this:

select column1, min(column2) as column2
from table
group by column1

for more than two columns I can suggest this:

select top 1 with ties
    t.column1, t.column2, t.column3
from table as t
order by row_number() over (partition by t.column1 order by t.column2)

take a look at this post https://stackoverflow.com/a/13652861/1744834

Upvotes: 10

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

You can use the ranking function ROW_NUMBER() to do this with a CTE. Especially, if there are more column other than these two column, it will give the distict values like so:

;WITH RankedCTE
AS
(
   SELECT *, ROW_NUMBER() OVER(PARTITION BY column1 ORDER BY Colmn2 ) rownum
   FROM Table
)
SELECT column1, column2
FROM RankedCTE
WHERE rownum = 1;

This will give you:

COLUMN1   COLUMN2
   a         1
   b         2
   c         1
   d         2
   e         5

SQL Fiddle Demo

Upvotes: 2

TwTw
TwTw

Reputation: 547

select MIN(col2),col1
from dbo.Table_1
group by col1

Upvotes: 1

bonCodigo
bonCodigo

Reputation: 14361

SELECT ColOne, Min(ColTwo) FROM Table GROUP BY ColOne ORDER BY ColOne

PS: not front of a,machine, but give above a try please.

Upvotes: 1

Related Questions