CODEWITHSUNDEEP
hadoophivehortonworks-data-platform
Varun Gupta
Varun Gupta

Reputation: 1457

Select distinct on one column but return all columns

My aim is to use distinct on one column but return all the columns.

My table is like this

id, name, year

1, John, 2012

2, Jake, 2012

3, Jenna, 2013

1, John, 2013

I need to do distinct on id column and return all the three columns as well as not same id's, I need to most recent record.

Output I need is

id, name, year

1, John, 2013

2, Jake, 2012

3, Jenna, 2013

I have tried these two commands

  1. select distinct id, name, year from sampletable. I will do distinct on all the rows.

  2. select * from sampletable group by id I will only return id column and drop the other columns.

Upvotes: 3

Views: 6669

Answers (2)

dimonb
dimonb

Reputation: 7

without analytic functions

    select t.id, t.name, t.year from t
    join
      (select id, max(year) year from t group by id) as S 
      on S.id=t.id and S.year = t.year

;

Upvotes: 0

libjack
libjack

Reputation: 6443

Using the windowing and analytic functions you can partition the data by the id ordering by the year, and choosing the first result:

SELECT id, name, year
FROM 
 (SELECT id, name, year, row_number() over (partition by id order by year desc) as r 
  FROM sampletable) S 
WHERE S.r = 1;

Plus there are other ranking functions such as rank and dense_rank for different selections.

Upvotes: 1

Related Questions