get articles with max date only

Forum|Forum|15 years ago
March 10, 2011
4 replies
2160 views

Hello Everyone

I've got a little problem with a sql query. I got two tables and a lot of articles. The articles are listed multiple times and got different dates. I want to select the following rows:

p.productname
i.amount
i.date

The problem is, that every product only should be listed one time (something like UNIC or DISTINCT). And it should be the product with the highest date. Is there something like MAX(date) that I can use?

What I already have is...

SELECT

p.productname,

i.amount,

i.date

FROM op_inventory i

LEFT JOIN products p

ON p.ItemID = i.fk_article

Now, how can I solve my problem above?...

Greets Dollique

Database access

This topic has been closed for replies.

D

Dan_Bracuk

Inspiring

Something like this should work with just about any database.

select some fields

from some tables

join

(select id, max(datefield) maxdate)

from sometables

where whatever

group by id) temp on sometable.id = temp.id and sometable.datefield.datefield = maxdate

etc

D

DolliqueAuthor

Participant

Thx, everyone.

I tried your solution but I made a mistake somewhere.

I don't really get what's wrong... But the idea with the subquery helped me a bit anyway. thx ^^

Here's what I have:

SELECT i.id, i.amount, i.date, p.productname FROM op_inventory i LEFT JOIN products p ON p.ItemID = i.fk_article JOIN ( SELECT id id_m, MAX(date) maxdate FROM op_inventory WHERE id_m = i.id GROUP BY id_m ) temp ON i.id = temp.id_m AND i.date = temp.maxdate <cfoutput>#gi_where#</cfoutput>  GROUP BY p.productname

Thx, for your help

D

Dan_Bracuk

Inspiring

What was the indication that you did something wrong?

In any event, if you have to do a left join from inventory to products, you might have a database problem. Also, the final group by clause is unnecessary. Plus, the stuff you commented out is redundant. If that's what your gi_where variable contains, it might be giving you a wrong answer.