CODEWITHSUNDEEP
sqlpostgresqlgroup-bygreatest-n-per-group
Ciro
Ciro

Reputation: 107

Extract column from SQL table based on another column if the same table

I m using POSTGRESQL.

Table of PURCHASES looks like this:

ID | CUSTOMER_ID | YEAR
1   1   2011
2   2   2012
3   2   2012
4   1   2013
5   3   2014
6   3   2014
7   3   2015

I need to extract 'ID' of the purchase with the latest 'date/year' for each CUSTOMER.

For example for CUSTOMER_ID 1 the year s 2013 which correcponds with id '4'.

I need to get ONE column as a return data structure.

PS. i m stuck with this kinda simple task )))

Upvotes: 0

Views: 124

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65158

Use row_number() analytic function with partition by customer_id to select by each customer with descending ordering by year ( if ties occur for year values [e.g. they're equal], then the below query brings the least ID values for each customer_id. e.g. 4, 2, 7 respectively )

WITH P2 AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY YEAR DESC) AS RN,
       *
  FROM PURCHASES 
)
SELECT ID FROM P2 WHERE RN = 1

Demo

Upvotes: 0

emsiiggy
emsiiggy

Reputation: 343

This should work, but doesn't look too pretty... 

SELECT DISTINCT ON(CUSTOMER_ID) ID FROM PURCHASES P
WHERE (CUSTOMER_ID,YEAR) = 
(SELECT CUSTOMER_ID,MAX(YEAR) FROM PURCHASES WHERE CUSTOMER_ID = P.CUSTOMER_ID 
GROUP BY CUSTOMER_ID);

So for input 

ID | CUSTOMER_ID | YEAR
1   1   2011
2   2   2012
3   2   2012
4   1   2013
5   3   2014
6   3   2014
7   3   2015

It will return

id
4
2
7

Meaning: 
For the lowest CUSTOMER_ID (it is 1) the id is 4 (year 2013)
Next we have CUSTOMER_ID (it is 2) the id is 2 (year 2012)
Lastly the CUSTOMER_ID (it is 3) the id is 7 (year 2015)

The idea behind this:

  1. Group by CUSTOMER_ID

  2. For each group select max(year)

  3. While looping over all records - if Customer_id and year equals those from number 2. then select ID from this record.

  4. Without DISTINCT ON(CUSTOMER_ID) it would return 2 records for CUSTOMER_ID = 2, because for both years 2012 it would find some records while looping.

If you write in the beginning instead of:

SELECT DISTINCT ON(CUSTOMER_ID) ID FROM PURCHASES P

this code:

SELECT DISTINCT ON(CUSTOMER_ID) * FROM PURCHASES P

then you will see everything clearly.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you want one row per customer, you can use distinct on:

select distinct on (customer_id) id
from purchases
order by customer_id, year desc;

This returns one column which is an id from the most recent year for that customer.

Upvotes: 1

Related Questions