CODEWITHSUNDEEP
mysql
steeveroucaute
steeveroucaute

Reputation: 69

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.

Upvotes: 0

Views: 28

Answers (1)

Amit Garg
Amit Garg

Reputation: 1218

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

Upvotes: 1

Related Questions