CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
pm100
pm100

Reputation: 50110

sql select with group by and join for lookup

Imagine I have a product table and an orders table. I want to list out the most recent order for each product

I imagine something like this

select name, description, price, max(date) 
from product 
  join order on order.item = product.name 
group by order.item

But my postgres DB complains that I cant have raw fields (sqlite doesnt complain) I need to have aggregate function. I can put min() for each column but that seems like a waste, given that all the values for a particular product are always the same. I wondered about 'distinct' but that doesnt seem to help here

NOTE - I need standard portable SQL , not specific to any given engine.

Upvotes: 1

Views: 803

Answers (4)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use correlated subquery :

select p.name, p.description, p.price, o.date
from product p inner join
     order o
     on o.item = p.name
where o.date = (select max(o1.date)
                from order o1
                where o1.item = p.name
               );

Upvotes: 0

wolf354
wolf354

Reputation: 167

If it was Oracle or MS you would need to group by all the fields in your select that aren't aggregate functions. It would be an extra line before "order by" with "group by p.name, description, price, date" ... About Postgres I am not so sure, but probably it will work.

Upvotes: 0

user330315
user330315

Reputation:

If you require standard ANSI SQL you can use a window function: 

select *
from (
    select p.name, p.description, p.price, 
           o.date, 
           max(o.date) over (partition by o.item) as last_date
    from product p
      join "order" o on o.item = p.name 
) t 
where date = last_date;

But in Postgres distinct on () is usually a lot faster.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

In Postgres, you can use distinct on:

select distinct on (o.item) p.name, description, price, date
from product p join
     order o
     on o.item = p.name
order by o.item, date desc;

I added aliases into the query. I strongly advise you to always qualify all column names. I would do that but I don't know where they come from in most cases.

Upvotes: 1

Related Questions