CODEWITHSUNDEEP
sql
Colin O'Donoghue
Colin O'Donoghue

Reputation: 27

SQL query for highest score and latest date combined

I have an SQL query that queryies a table that holds test scores

the table is set up as follows an ID(which is the Primary Key), Username, UserID, Test_name, Date_Taken, Time_Taken, Num_questions, Grade, Time_Per_questionand, Test_Slug.

I have the following SQL query that gives me the max score per test name for a specific user:

SELECT t.ID, 
       t.test_Name, 
       t.UserID, 
       t.Test_Slug, 
       t.Grade, 
       t.date_taken 
FROM   academytestsite.testscores t 
       JOIN (SELECT hs.test_Name, 
                    hs.UserID, 
                    hs.test_Slug, 
                    Max(hs.Grade) AS MaxGrade 
             FROM   academytestsite.testscores hs 
             GROUP  BY hs.test_Name, 
                       hs.UserID) mhs 
         ON mhs.test_Name = t.test_Name 
            AND mhs.UserID = t.UserID 
            AND mhs.MaxGrade = t.Grade 
WHERE  t.UserID = 5608 
ORDER  BY test_name

The problem i have is if the user has multiple Grades that are the same for the same test.

I want the query to only give the newest Grade whereas the statement gives all results that are the same. Hope this makes sense

I have tried grouping by test_name but this gives the first result per test in the table and not the newest.

Upvotes: 2

Views: 1802

Answers (4)

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

To get the highest grade per user and test, you may use a self left join

select t1.* from testscores t1
left join testscores t2 on
t1.Username = t2.Username
and 
t1.test_name = t2.test_name
and t1.grade < t2.grade 
where t2.id is null
ORDER  BY t1.test_name

To get the newest taken date in a duplicate same grade, a MAX sub query will be helpful

select t1.* from testscores t1
left join testscores t2 on
t1.Username = t2.Username
and 
t1.test_name = t2.test_name
and t1.grade < t2.grade 
where t2.id is null
and t1.Date_Taken = 
    (select max(t3.Date_Taken) 
     from testscores t3 
     where t3.Username = t1.Username 
     and t3.test_name = t1.test_name 
     and t3.grade = t1.grade
     group by t3.Username , t3.test_name
     )
ORDER  BY t1.test_name

For performance reasons, you may also see distinct result, if just user, test and grade matters:

select distinct t1.Username , t1.test_name , t1.Grade from testscores t1
left join testscores t2 on
t1.Username = t2.Username
and 
t1.test_name = t2.test_name
and t1.grade < t2.grade 
where t2.id is null
ORDER  BY t1.test_name

Fiddle Demo

Upvotes: 0

Ruslan Veselov
Ruslan Veselov

Reputation: 337

Try this:

SELECT *
FROM academytestsite.testscores t 
INNER JOIN (
   SELECT 
      UserID, 
      test_Name, 
      MAX(date_taken) as date_taken
   FROM academytestsite.testscores
   GROUP BY UserID, test_Name
) mhs ON (mhs.UserID = t.UserID) 
     AND (mhs.test_Name = t.test_Name) 
     AND (mhs.date_taken = t.date_taken)
ORDER BY t.test_Name

See SQL demo

Upvotes: 0

Dan
Dan

Reputation: 4502

Most SQL dialects, MySQL included, lack any way of directly saying "give me the best row for each value of a particular column". The other answers posted both give you a way to say "give me a row for each value of a particular column where other columns equal the best." You can also say "give me each row where no better rows exist," which is what I've done below.

The technique for this is to use a LEFT JOIN to find better rows, and then use a WHERE clause to only select rows where the better rows' ID is NULL. 

SELECT t.ID, 
       t.test_Name, 
       t.UserID, 
       t.Test_Slug, 
       t.Grade, 
       t.date_taken 
FROM   academytestsite.testscores t
LEFT JOIN academytestsite.testscores better_row
       ON better_row.test_Name = t.test_Name
      AND better_row.UserID = t.UserID
      AND (better_row.Grade > t.Grade
           OR (better_row.Grade = t.Grade AND better_row.date_taken > t.date_taken))
WHERE better_row.ID IS NULL

This version will find each test score with the highest grade, using the date_taken column as a tiebreaker when two grades match. You can change the criteria for the "best" row by changing the join condition. For example, if you just wanted the most recent grade for each test, you could use

      AND better_row.date_taken > t.date_taken

If you get multiple results using this method, then you need to adjust the join condition such that only one row for each (test_Name, UserID) pair can be selected as the "best" one. If you have duplicate grades and dates, could achieve this by making the primary key a tie breaker:

       AND (better_row.Grade > t.Grade
           OR (better_row.Grade = t.Grade AND better_row.date_taken > t.date_taken)
           OR (better_row.Grade = t.Grade AND better_row.date_taken = t.date_taken 
               AND better_row.ID > t.Id))

Upvotes: 3

Uncle Iroh
Uncle Iroh

Reputation: 6045

This should also work:

select grade, username, date_taken, time_taken from(
select grade, username, date_taken, time_taken from test_scores a
where a.grade = (select max(grade) from test_scores b where b.username = a.username group by username)
order by date_taken desc, time_taken desc) temp
group by username;

http://sqlfiddle.com/#!2/550d7b/3

Upvotes: 0

Related Questions