UKC

It has been a while since I have done any SQL and am a bit rusty. I am mucking about for my own benefit/education.

I have a mySql db with a price history table for some products.

product_code
price_date
price
category
description
...

For a given product_code there may be many rows, each with a different price_date. I want to select the row with the most recent price_date from a query such as ...

select description, product_code, price from thisTbl where description like "%marmite%"

--clearly the above query will return all matching rows, but I want just one per product_code, the row with the most recent date.

How should I do this?

Pointers much appreciated.

In reply to lowersharpnose:

SELECT * FROM table WHERE description LIKE %marmite% ORDER BY price_date DESC LIMIT 1;

Replace * with the column names you want to pull out. The natural sort order is ascending so you want descending to get the most recent first and "limit 1" just gets the first record. (DESC may not be the correct term but your version of SQL should have something similar)

Don't ask me for anything more complicated though!

In reply to lowersharpnose:

That example won't work very well as you'd have to run it for every product separately.

Instead you need to add a subquery - ie something like:

select t.description, t.product_code, price from thisTbl t where t.description like "%marmite%"
and t.price_date = (Select max(price_date) from thisTbl where product_code = t.product_code)

Now you get one row per product
Cheers

In reply to Bob:

I have more than one product with the word marmite in the description and I want the most recent price for each of them.

12345, 1-8-2014, 1.23, marmite twiglets
12345, 2-8-2014, 1.25, marmite twiglets
12345, 5-8-2014, 1.20, marmite twiglets
12346, 1-8-2014, 2.23, marmite jar
12346, 3-8-2014, 2.00, marmite jar
12347, 1-8-2014, 1.93, marmite soap
12347, 3-8-2014, 1.93, marmite soap

I want three rows returned:
12345, 5-8-2014, 1.20, marmite twiglets
12346, 3-8-2014, 2.00, marmite jar
12347, 3-8-2014, 1.93, marmite soap


I think your query returns the row that has the most recent price_date that has marmite in the description.

Does that make sense?

In reply to johnmctighe:

Thanks,I will try that.

In reply to johnmctighe:
Oops, missed the "per product_code" bit

Do you need to check the description? surely the product code is the unique part, you aren't going to have marmite and marmalade having the same product code.

select t.description, t.product_code, price from thisTbl t where t.price_date = (Select max(price_date) from thisTbl where product_code = t.product_code)

Edit: just seen your subsequent post

In reply to lowersharpnose:

That's the weirdest date format for storage I've seen! Stick with ISO 8601 it's naturally sortable: 2014-8-5.

In reply to Bob:

I was making up the dates for the example - as opposed to cutting and pasting real data, that's why they look odd.

In reply to lowersharpnose:
Use of the miraculous sql fiddle should be mandatory for all sql queries on ukc!

http://sqlfiddle.com/#!2/b4963/3/0

I copy and pasted your fake data then fiddled with the sql to get what might be a solution. Sql fiddle seems to have interpreted the prices as integers and the dates look flakey but i didn't bother fixing that.

Select *, max(Date) from Table1 as dd group by Pid;

Sql inspired by
http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group.html

In reply to lowersharpnose:
http://sqlfiddle.com/#!2/db970e/1/0

Prices as float

Hmmm, sql fiddle is miraculous but my sql isn't.

In reply to elsewhere:

I like the idea of sqlfiddle, thanks.

But, your query does not get the desired result. It gets the latest date for each product, but not the price associated with that date.

In reply to lowersharpnose:

Yes, just noticed that. Looks like subqueries required after all.

http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.htm...

In reply to lowersharpnose:

http://sqlfiddle.com/#!2/7c95a/8/0

Although mysql manual http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.htm... suggests joins more efficient

In reply to elsewhere:

Thanks.

In reply to lowersharpnose: 1) Strictly speaking you should normalise your data - ProductID, Description and Category belong in different table to the Price information- ProductID, Date and Price - otherwise you have repeating groups/transitive dependencies.

2) Subqueries are the way to go. To list all products with their latest prices here is the SQL, where TempTable is the name of the non-normalised table containing the product and price information:

Select

T.Product_Code
,T.Description
,T.Price

From TempTable T

Where T.Price_Date = (Select MAX(Price_Date) as MaxDate
From TempTable T1
Where T1.Product_Code = T.Product_Code)

Just add another Where clause if you want to return just the latest price for a specific product.

In reply to Rob Exile Ward:

Have you tried using an alias table, like so:

WITH tempTable (prod, date, pri, cat, desc, rownum) AS (
SELECT product_code AS prod,
price_date AS date,
price AS pri,
category AS cat,
description AS des,
ROW_NUMBER() OVER (PARTITION by product_code
ORDER by price_date DESC) AS rownum)
FROM thisTbl
)
SELECT prod, date, pri, cat, desc
FROM tempTable
WHERE rownum = 1


This works for me (db2 database). You might need to find some other way of realising the ROW_NUMBER() column if that doesn't work for you.

In reply to Trevers: Bit complicated isn't it for something (subqueries) that are built in to SQL 92?

In reply to Rob Exile Ward:

I don't think (may be wrong) db2 would allow T.product_code within the subquery when the table 'T' is defined outside.