CODEWITHSUNDEEP
sqlsqlitesubqueryself-join
shotdsherrif
shotdsherrif

Reputation: 563

sqlite self join query using max()

Given the following table: 

| id | user_id | score |       date |
|----|---------|-------|------------|
|  1 |       1 |     1 | 2017-08-31 |
|  2 |       1 |     1 | 2017-09-01 |
|  3 |       2 |     2 | 2017-09-01 |
|  4 |       1 |     2 | 2017-09-02 |
|  5 |       2 |     2 | 2017-09-02 |
|  6 |       3 |     1 | 2017-09-02 |

Need to find the user_ids that have the max score for any given date (there can be more than one), so I'm trying:

  SELECT s1.user_id
  FROM (
     SELECT max(score) as max, user_id, date
     FROM scores 
  ) AS s2 
  INNER JOIN scores as s1
  ON s1.date = '2017-08-31'
  AND s1.score = s2.max

The query returns correctly for the last 2 dates but returns 0 records for the first date ('2107-08-31'), it should return the score of 1

Why won't that first date return correctly and/or is there a more elegant way of writing this query?

Here is the version of the query that comes closest to working, though it does not work when there is only one test score. I do not understand how I am getting away with not using the GROUP BY clause in the aggregate.

SELECT s1.user_id
  FROM (
    SELECT max(score) as max, user_id, date
    FROM scores
  ) AS s2
  INNER JOIN scores as s1
  ON s1.date = :date
  AND s1.score = s2.max

Upvotes: 0

Views: 175

Answers (1)

Paul Coldrey
Paul Coldrey

Reputation: 1439

A correct query option is:

SELECT user_id
FROM scores 
WHERE score = (SELECT MAX(score) FROM scores WHERE date = '2017-08-01')

Note that one issue with your query (which is probably your issue) is that the user_id and date in the sub query are not going to be related to the row that contains MAX(score) since you don't have any "group by" clause to force grouping

Upvotes: 2

Related Questions