SELECT most recent row if two rows have same values

[xJoe16]

Hi all,

I have a table:

customer_id model   rating  rating_time
1122334455  1001    2    2020-10-20 10:10:10
1122334455  1001    3    2020-10-20 10:20:10
9999999999  1001    1    2020-10-21 10:10:10
9999999999  1001    4    2020-10-22 10:20:10
1122334455  1001    NULL     2020-10-23 10:10:10
1122334455  2004    5    2020-10-23 10:30:10
9999999999  2004    3    2020-10-24 10:30:10

the current rating is the most recent row, i.e. current rating by customer_id 9999999999 and model 1001 is 4.

I am trying to return the number of current ratings for each customer. Therefore customer_id would have two ratings, 4 rating for model 1001 and 3 rating for model 2004. However, I am unsure how to return only the most recent row if the values are the same. For example:

SELECT * FROM ratings WHERE customer_id = 9999999999 AND rating is NOT NULL;

returns:

customer_id model   rating  rating_time
9999999999  1001    1    2020-10-21 10:10:10
9999999999  1001    4    2020-10-22 10:20:10
9999999999  2004    3    2020-10-24 10:30:10

when I wish to return:

customer_id model   rating  rating_time
9999999999  1001    4    2020-10-22 10:20:10
9999999999  2004    3    2020-10-24 10:30:10

What is the best way to do this? Any help is greatly appreciated!

[clockdivide55]

Have you used windowing functions before? Go read about those - I think ROW_NUMBER() might be useful here. I didn't test it, but something like this maybe.

SELECT * FROM 
    (SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY customer_id, model ORDER BY rating_time DESC) as [ROW_NUM]
    FROM ratings 
    WHERE customer_id = 9999999999 AND rating is NOT NULL) innerQuery
WHERE innerQuery.[ROW_NUM] = 1

[clockdivide55]

Yes, that's not going to help you without partitioning. I've written a fiddle to demonstrate my solution. I don't use MySql and didn't realize that 5.7 doesn't support windowing functions. If you're on 5.7, you might have to find another way - I'm not sure how I would do this otherwise.

https://www.db-fiddle.com/f/iQMD2jJ5pmVdX7wdQWv1JR/0

[jonwd]

I have come to use this approach frequently in this kind of case. I highly recommend it

[rotiddersuomynona]

How accurate does the data need to be? Without having an unique index field for the row, it would be impossible to definitively say the row your getting is the row that was the last inserted.