I am doing a SQL course, and I have the following table:

which I would like to filter its results ONLY if the latest timestamp and rating values are both the highest ones, for each player_store_id, so in this case, as expected result, rows 3 and 4 should be filtered.

How can I do it? tried with many different options, also searching, but didn't manage to make it work.

SELECT game_id, player_store_id, rating, timestamp
FROM #duplicated_reviews_date_and_rating
WHERE player_store_id IN (SELECT DISTINCT player_store_id FROM #duplicated_reviews_date_and_rating)
AND rating = (SELECT max(rating) FROM #duplicated_reviews_date_and_rating)
AND #duplicated_reviews_date_and_rating.timestamp = (SELECT max(#duplicated_reviews_date_and_rating.timestamp) FROM #duplicated_reviews_date_and_rating)

Only gives me the results for a single 'player_store_id'.

Everything I tried that I could find here, didn't work, gave errors, or was too complicated for my current level.

Thank you very much!

CodePudding user response：

I can give you an idea

SELECT * FROM t WHERE myValue IN (SELECT max(myValue) From t);

Demo: http://sqlfiddle.com/#!9/6135ad/1

You can adapt it to your needs

CodePudding user response：

use group by :

select player_store_id, max(myValue), game_id from table group by player_store_id, game_id;

CodePudding user response：

I Am not sure Whether I understand you question correctly.

You can try below Approaches. Below queries working fine in MS SQL Server. Please change the syntax according to MY SQL

Approch 1:

select * from 
review r
Join(
    select player_store_id,max(rating) rating,max(timestamp1) timestamp1
    from review
    group by player_store_id
) a
on r.player_store_id=a.player_store_id
and r.rating = a.rating
and r.timestamp1 = a.timestamp1

Approch 2:

WITH CTE
AS(
   SELECT *,ROW_NUMBER() OVER (PARTITION BY  player_store_id ORDER BY RATING DESC,TIMESTAMP1 DESC) AS RNO FROM review
  )

  SELECT * FROM CTE WHERE RNO = 1

CodePudding user response：

Since the requirement is to get rows with timestamp and rating values that are both the highest ones, you can:

1. use a subquery to get the highest timestamp and rating for each group,
2. join with the original table to get the whole rows
3. on the condition that both its rating and timestamp are the highest for its group.

SELECT all_rows.*
FROM duplicated_reviews_date_and_rating all_rows
JOIN (
  SELECT max(rating) max_rating, max(timestamp) max_timestamp
  FROM duplicated_reviews_date_and_rating
  GROUP BY player_store_id
) highest
ON all_rows.rating = highest.max_rating
AND all_rows.timestamp = highest.max_timestamp;

Or even simpler, use an IN clause with multiple columns

SELECT *
FROM duplicated_reviews_date_and_rating
WHERE (rating, timestamp) IN (
  SELECT max(rating), max(timestamp)
  FROM duplicated_reviews_date_and_rating
  GROUP BY player_store_id
);

Demo: https://onecompiler.com/mysql/3xy78e9x9