Query HELP!

Ripper · 7 Feb 2008 at 16:46

This is doing my nut in...

OK I have a table called UserTest which stores users' test results. The fields are UserTestID(PK), TestID(FK), Score, Username(FK).

Now basically I want a control (dropdownlist, gridview etc (anything that will work really)) which populates with each users' best score for a test (determined by another control [fine with this bit])

At the moment (since a user can have multiple results for a single test) I can only get it to list each distinct score, which leads to usernames being listed more than once since some have attempted a test more than once and got different scores. Query at the moment is below:

Code:

SELECT DISTINCT [TestID], [Score], [UserName] FROM [UserTest] WHERE ([TestID] = @TestID)

Research leads me to believe this can be achieved by combining distinct and MAX but I've tried it and I'm getting nowhere.

Anyone help me?

chesterstu · 7 Feb 2008 at 17:19

I would have to look it up for the exact syntax but groupby and order by score desc

Ripper · 7 Feb 2008 at 17:33

But wouldn't that list all scores rather than the single highest value?

Haircut · 7 Feb 2008 at 17:38

Code:

SELECT TestID, Score, UserName 
FROM UserTest ut
WHERE TestID = @TestID
AND UserName = @UserName
AND Score =
(SELECT MAX(Score)
 FROM UserTest ut2
 WHERE ut2.UserName = ut.UserName 
 AND ut2.TestID = ut.TestID
)

Should do it.
This is known as a correlated subquery

Is this the sort of thing you want?

EDIT: Also should point out that you can just remove the line AND UserName = @username to do it for all users.

Ripper · 7 Feb 2008 at 17:47

Cheers. It seems to work. Can you explain the "ut" bits?

Haircut · 7 Feb 2008 at 17:49

They're just aliases for the tables.
Because you have two tables with the same name in the query you have to create aliases - I just chose ut and ut2 as alias names for your table.

Ripper · 7 Feb 2008 at 17:53

Haircut said:
EDIT: Also should point out that you can just remove the line AND UserName = @UserName to do it for all users.

So if I did that it'd just pull out the user with the highest score for a test?

If so thats really useful, cheers.

Ripper · 7 Feb 2008 at 18:31

Ok, I had to edit the query for it to work how I wanted (to get charts displaying properly). The query is being used as a datasource for two dropdownlists, where a user will select a user from each and a chart is generated to compare their scores. The query is now:

Code:

SELECT TestID, Score, UserName 
FROM UserTest ut
WHERE Score =
(SELECT MAX(Score)
 FROM UserTest ut2
WHERE ut2.UserName = ut.UserName)
AND (TestID = @TestID)

@TestID represents a dropdownlist listing all the available tests. Now, when I select a test I want the other two dropdownlists to display only users who have attempted that test. At the moment it lists the same users no matter what is selected in the dropdownlist (but it does select the right score for each test).

How can I get the two dropdownlists to dynamically update so to speak?

Am I right in thinking something needs to be put in the selectedindexchanged event of the test selection dropdownlist?

Everything is inside an AJAX updatepanel if that makes any difference.