CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Henry
Henry

Reputation: 259

How to select the last record of each ID

I need to extract the last records of each user from the table. The table schema is like below.

mytable

product | user_id |
-------------------
   A    |   15    |
   B    |   15    |
   A    |   16    |
   C    |   16    |
-------------------

The output I want to get is

product | user_id |
-------------------
   B    |   15    |
   C    |   16    |

Basically the last records of each user.

Thanks in advance!

Upvotes: 5

Views: 26519

Answers (5)

Subarata Talukder
Subarata Talukder

Reputation: 6331

In this query I am going to explain each steps:

Based on your table structure lets first create a temp table: [That you already have]

CREATE TABLE #mytable (
    product CHAR(1),
    user_id INT
);

Inserted some values above like:

INSERT INTO #mytable (product, user_id)
VALUES
    ('A', 15),
    ('B', 15),
    ('A', 16),
    ('C', 16);

Now write the query to getting your expected results:

SELECT TOP 1 WITH TIES product, user_id
FROM #mytable
ORDER BY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY product DESC)

DROP TABLE IF EXISTS #mytable --Drop the temp table

Hope you can easily understand. Enjoy with the code.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271141

There is no such thing as a "last" record unless you have a column that specifies the ordering. SQL tables represent unordered sets (well technically, multisets).

If you have such a column, then use distinct on:

select distinct on (user_id) t.*
from t
order by user_id, <ordering col> desc;

Distinct on is a very handy Postgres extension that returns one row per "group". It is the first row based on the ordering specified in the order by clause.

Upvotes: 4

Srijon Chakraborty
Srijon Chakraborty

Reputation: 2164

You can use a window function called ROW_NUMBER.Here is a solution for you given below. I have also made a demo query in db-fiddle for you. Please check link Demo Code in DB-Fiddle

WITH CTE AS
(SELECT product, user_id,
       ROW_NUMBER() OVER(PARTITION BY user_id order by product desc)
       as RN
FROM Mytable)
SELECT product, user_id FROM CTE WHERE RN=1 ;

Upvotes: 6

Gabriel Santana
Gabriel Santana

Reputation: 131

You should have a column that stores the insertion order. Whether through auto increment or a value with date and time.

Ex:

autoIncrement produt user_id
1 A 15
2 B 15
3 A 16
4 C 16 
SELECT produt, user_id FROM table inner join 
     ( SELECT MAX(autoIncrement) as id FROM table group by user_id ) as table_Aux
     ON table.autoIncrement = table_Aux.id

Upvotes: 0

Fahmi
Fahmi

Reputation: 37493

You can try using row_number()

select product,iserid
from
(
select product, userid,row_number() over(partition by userid order by product desc) as rn
from tablename
)A where rn=1

Upvotes: 2

Related Questions