steeveroucaute steeveroucaute - 4 months ago 18
MySQL Question

Select latest row from a group of options MySQL

I have a table that contains a set of options and relevant pricing. We have versioning implemented which means that we store several rows for the same option with different timestamps. Everytime we make an update to the pricing, a new row is created with a timestamp.

I am using the query below:

SELECT
localizedOptionID,
uProfileID,
sOptionID,
MAX(datestamp),
localOptionName,
quantities
FROM
localisedProductOptions
WHERE
uProfileID = 2
AND sOptionID in ('BGCD','Q6G1','3BET')
GROUP BY sOptionID
ORDER BY MAX(datestamp)


It seems to be returning only one version of each row, but unfortunately it does not seem to be the latest.

Can anyone help me setting up the query? All the data is contained within one single table, so I am not using any join.

Answer

You can try the below mentioned query to get desire data

    select   localizedOptionID, uProfileID,sOptionID, datestamp,localOptionName, 
quantities from localisedProductOptions WHERE uProfileID = 2  AND sOptionID in 
('BGCD','Q6G1','3BET') and datestamp in(select MAX(datestamp) from
 localisedProductOptions where  uProfileID = 2 AND sOptionID in 
('BGCD','Q6G1','3BET') GROUP BY sOptionID);
Comments