CODEWITHSUNDEEP
sqloracle-databasedatesubquerygreatest-n-per-group
Nani220
Nani220

Reputation: 61

SQL only select rows with max date within each user

SQL beginner here. I've got a simple test that users take, and each row is the answer to one of their questions. They're allowed to take the exam once per day, so some people take it a second time on another day, and thus will have many rows with different test dates. What I'm basically trying to do is get each user's most recent score.

Here is what my data looks like (table name is dumdum):

+----------+----------------+----------+------------------+
| USERNAME | CORRECT_ANSWER | RESPONSE | DATE_TAKEN       |
+----------+----------------+----------+------------------+
| matt     | 1              | 1        | 3/23/15 1:04:26  |
| matt     | 2              | 2        | 3/23/15 1:04:28  |
| matt     | 3              | 3        | 3/23/15 1:04:23  |
| david    | 1              | 3        | 3/20/15 1:04:25  |
| david    | 2              | 2        | 3/20/15 1:04:28  |
| david    | 3              | 1        | 3/20/15 1:04:30  |
| david    | 1              | 1        | 3/21/15 11:03:14 |
| david    | 2              | 3        | 3/21/15 11:03:17 |
| david    | 3              | 2        | 3/21/15 11:03:19 |
| chris    | 1              | 2        | 3/17/15 12:45:52 |
| chris    | 2              | 2        | 3/17/15 12:45:56 |
| chris    | 3              | 3        | 3/17/15 12:45:59 |
| peter    | 1              | 1        | 3/19/15 2:45:33  |
| peter    | 2              | 3        | 3/19/15 2:45:35  |
| peter    | 3              | 2        | 3/19/15 2:45:38  |
| peter    | 1              | 1        | 3/20/15 12:32:04 |
| peter    | 2              | 2        | 3/20/15 12:32:05 |
| peter    | 3              | 3        | 3/20/15 12:32:05 |
+----------+----------------+----------+------------------+

and what I'm trying to get in the end...

+----------+------------------+-------+
| USERNAME | MOST_RECENT_TEST | SCORE |
+----------+------------------+-------+
| matt     | 3/23/2015        | 100   |
| david    | 3/21/2015        | 33    |
| chris    | 3/17/2015        | 67    |
| peter    | 3/20/2015        | 100   |
+----------+------------------+-------+

I ran into some trouble because I need to go by day, and not by day/time, so I had to do a weird maneuver where I went to character and back to date... This is what I have so far, but I can't figure out how to use only the scores from the most recent test (right now it's factoring in all scores from every test ever taken)...

SELECT username, to_date(substr(max(test_date),1,9),'dd-MON-yy') as most_recent_test, round((sum(case when response=correct_answer then 1 end)/3)*100,0) as score
FROM dumdum group by username

Any help would be appreciated! Thanks!

Upvotes: 1

Views: 175

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52645

There are several solutions to this problem this one uses the WITH clause and the RANK function.

It also uses the TRUNC function rather than to_date(substr( 

 with mxDate as 
 (SELECT USERNAME,
        TRUNC(DATE_TAKEN) as MOST_RECENT_TEST,
        CASE WHEN CORRECT_ANSWER = RESPONSE THEN 1 else 0 END as SCORE,
        RANK () OVER (PARTITION BY  USERNAME
                           ORDER BY TRUNC(DATE_TAKEN)  DESC) Rk
 FROM dumdum)
SELECT
   USERNAME,
   MOST_RECENT_TEST,
   SUM(SCORE)/3 * 100

FROM
   mxDate 
WHERE 
  rk = 1
GROUP BY 
   USERNAME,
   MOST_RECENT_TEST

Demo

Upvotes: 3

Related Questions