CODEWITHSUNDEEP
sql
laurie
laurie

Reputation: 6251

SQL select distinct rows

I have data like this (col2 is of type Date)

| col1 |        col2         |
------------------------------
|  1   | 17/10/2007 07:19:07 |
|  1   | 17/10/2007 07:18:56 |
|  1   | 31/12/2070          |
|  2   | 28/11/2008 15:23:14 |
|  2   | 31/12/2070          |

How would select rows which col1 is distinct and the value of col2 is the greatest. Like this

| col1 |        col2         |
------------------------------
|  1   | 31/12/2070          |
|  2   | 31/12/2070          |

Upvotes: 4

Views: 2834

Answers (6)

Quassnoi
Quassnoi

Reputation: 425753

In Oracle and MS SQL:

SELECT  *
FROM    (
        SELECT  t.*, ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2 DESC) rn
        FROM    table t
        ) q
WHERE rn = 1

This will select other columns along with col1 and col2

Upvotes: 0

Gustavo
Gustavo

Reputation: 359

select col1, max(col2) from MyTable
group by col1

Upvotes: 3

Crab Bucket
Crab Bucket

Reputation: 6277

i reckon it would be

select col1, max(col2) from DemoTable group by col1

unless i've missed something obvious

Upvotes: 3

KM.
KM.

Reputation: 103697

SELECT Col1, MAX(Col2) FROM YourTable GROUP BY Col1

Upvotes: 2

Tom Ritter
Tom Ritter

Reputation: 101400

 select col1, max(col2)
 from table
 group by col1

Upvotes: 4

Milen A. Radev
Milen A. Radev

Reputation: 62663

SELECT col1, MAX(col2) FROM some_table GROUP BY col1;

Upvotes: 17

Related Questions