By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,162 Members | 1,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,162 IT Pros & Developers. It's quick & easy.

SQL Query

100+
P: 347
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

Expand|Select|Wrap|Line Numbers
  1. 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 thei 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 he newest.

Any help would be appreciated

thanks
Aug 18 '14 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,369
What you want is a query that returns the max date for each test. You can then join that to the test results table to retrieve just the most current test result for each test.
Aug 18 '14 #2

Post your reply

Sign in to post your reply or Sign up for a free account.