CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Anthony
Anthony

Reputation: 35928

How to get most recent item by date in a group by clause

I am running the following query to get the most recent record based on date

select myItem,to_timestamp(my_date/1000) 
from history_table
where to_timestamp(sign_date/1000)::date >= '2019-01-01' 
    and myItem = 'SomeItem' 
group by myItem,to_timestamp(my_date/1000) 
order by to_timestamp(my_date/1000) 
DESC LIMIT 1;

This returns data like this:

         myItem          |      to_timestamp
----------------------+------------------------
 SomeItem                | 2019-02-20 15:37:57+00
(1 row)

But if I remove the LIMIT 1, then I get this:

         myItem          |      to_timestamp
----------------------+------------------------
 SomeItem             | 2019-02-20 15:37:57+00
 SomeItem             | 2019-02-15 13:47:43+00
 SomeItem             | 2019-02-08 19:02:57+00
 SomeItem             | 2019-02-08 12:42:34+00
 SomeItem             | 2019-02-07 21:07:16+00
 SomeItem             | 2019-02-07 21:04:26+00
 SomeItem             | 2019-02-04 22:01:42+00
(7 rows)

Question

I would like to remove the myItem = 'SomeItem' clause so that I can get the most recent record for each item based on my_date. How can I do this?

Upvotes: 0

Views: 106

Answers (3)

Alexander Shapkin
Alexander Shapkin

Reputation: 1262

  1. You shouldn't group by date. Only your item should determine the group.
  2. And dont convert value entire max, coz it perform it for every row in group and after that determine the max. Convert only max value - it should be faster

In T-Sql:

select
    myItem,
    to_timestamp(MAX(my_date)/1000) as my_date_Timestamp
from history_table
Group by myItem
Order by my_date_Timestamp DESC

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

In PostgreSQL you use DISTINCT ON for this:

select distinct on (myItem) myItem, to_timestamp(my_date/1000)
from history_table
where to_timestamp(sign_date/1000)::date >= date '2019-01-01' 
order by myItem, to_timestamp(my_date/1000) desc;

You can add more columns to the select clause, as you are retrieving the latest row per myitem.

EDIT: If it's just about showing the maximum date per myitem, I'd go with Cristian's anwer. My solution is appropriate when there are more columns in the table you want to show.

Upvotes: 1

Cristian
Cristian

Reputation: 44

Try using MAX(to_timestamp(my_date/1000)), query:

select myItem, MAX(to_timestamp(my_date/1000))
from history_table
where to_timestamp(sign_date/1000)::date >= '2019-01-01'9 
group by myItem
order by myitem;

Upvotes: 1

Related Questions