sql select with group by and join for lookup

Question

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.

[Group by clause in mySQL and postgreSQL, why the error in postgreSQL?](https://stackoverflow.com/questions/33629168/group-by-clause-in-mysql-and-postgresql-why-the-error-in-postgresql) and `SQL99 and later permits such nonaggregates per optional feature T301 if they are functionally dependent on GROUP BY columns` — Lukasz Szozda, Aug 23 '18 at 16:19

score 1 · Answer 1 · answered Aug 23 '18 at 16:19

1

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.

answered Aug 23 '18 at 16:19

Gordon Linoff

1,242,037
58
646
786

1

I updated ,my question. Ideally I need standard sql, I have to run the query through various engines – pm100 Aug 23 '18 at 16:21

score 1 · Answer 2 · answered Aug 23 '18 at 16:25

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.

score 0 · Accepted Answer · answered Aug 23 '18 at 16:27

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.

score 0 · Answer 4 · answered Aug 23 '18 at 17:01

0

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
               );

answered Aug 23 '18 at 17:01

Yogesh Sharma

49,870
5
26
52

That will be slow for big tables, at least in PostgreSQL. – Laurenz Albe Aug 23 '18 at 17:10
@LaurenzAlbe. . . This is the alternative way for ANSI SQL. – Yogesh Sharma Aug 23 '18 at 17:43

sql select with group by and join for lookup

4 Answers4