How to select distinct rows with a specified condition

Question

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!

Community · Accepted Answer · 2017-05-23 10:27:52Z

10

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

edited May 23, 2017 at 10:27

CommunityBot

11 silver badge

answered Dec 2, 2012 at 7:58

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andrew Over a year ago

I tried this approach (except for my actual table contains more columns), but it just gave me all rows

roman Over a year ago

well this approach is good for two columns. I'll add my favourite approach for more than two columns in a second

Michael Dunlap Over a year ago

@Andrew Can you please update your original post with your actual schema and all the rows you want to select?

Michael Dunlap Over a year ago

@Andrew And what do you want to see as the result of the query?

Mahmoud Gamal · Accepted Answer · 2012-12-02 08:02:36Z

2

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

edited Dec 2, 2012 at 8:02

answered Dec 2, 2012 at 7:57

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

3 Comments

Michael Dunlap Over a year ago

This is beyond overkill for his use case.

roman Over a year ago

@DigitalD I wanted to add this comment, but then it turns out that OP had more than 2 columns so this approach is actually useful for him

Michael Dunlap Over a year ago

@RomanPekar I see that now. He didn't specify that infomation initially

bonCodigo · Accepted Answer · 2012-12-02 08:00:57Z

1

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

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

answered Dec 2, 2012 at 8:00

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

Comments

TwTw · Accepted Answer · 2012-12-02 08:01:33Z

1

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

answered Dec 2, 2012 at 8:01

TwTw

5474 silver badges16 bronze badges

Collectives™ on Stack Overflow

How to select distinct rows with a specified condition

4 Answers 4

4 Comments

SQL Fiddle Demo

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related