Group By in sql causes additional records

This seems like such a simple problem, but I seem to be going around in circles.  I have a View which has the following fields :

attemptid  INT
NOQs INT
Total INT
total_percent FLOAT
userid INT
started DATETIME

What I want is to get a list of attemptid s for the maximum total_percent per user with a filter on started. I will then be constucting a result based on that list of IDs.

As soon as I include attemptid in the output, I get multiple records per user:

SELECT     MAX(Total) AS Expr1, attemptid, userid
FROM         all_results_by_attemptid
WHERE     (started < '16 jun 2011')
GROUP BY userid, attemptid

This seems so simple and I am embarrassed to be asking :) Any help appreciated.


spanoutAsked:
Who is Participating?
 
jogosConnect With a Mentor Commented:
That's because for user 432 there are 2 attemptid's that have that max total of 12, isn't that worth to know?

select  a.total , min(a.attemptid),a.userid
from all_rsults_by_attemptid  as a
inner join
  ( SELECT     MAX(Total) AS Expr1, userid
    FROM         all_results_by_attemptid
   WHERE     (started < '16 jun 2011')
 GROUP BY userid) as x on a.userid = x.userid and a.total = x.expr1
group by a.userid,a.total
order by a.userid

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Can you give a short example of data you have, and you expect to get? Because if you group by userid and attemptid, of course you get more records as if you group only by one of those.
0
 
spanoutAuthor Commented:
The data that I am hoping for is the attemptid of the record that has the max total_percent figure for each user within a given date range (the started field). So a user may have 2 attempts in the date range, we want to find the one that has the maximum score and then use the id of that attempt to query some other data. Does that clarify?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
jogosCommented:
Finding the max must be done separate from finding the data that goes with it.

select  a.total , a.attemptid,a.userid
from all_rsults_by_attemptid  as a
inner join
  ( SELECT     MAX(Total) AS Expr1, userid
    FROM         all_results_by_attemptid
   WHERE     (started < '16 jun 2011')
 GROUP BY userid) as x on a.userid = x.userid and a.total = x.expr1 

Open in new window

0
 
spanoutAuthor Commented:
Thanks for that. Unfortunately I am getting multiple records per user. I enclose the view data and the query result in this xls file data.xlsx
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
select all.*
from all_results_by_attemptid all
join 
  (SELECT     MAX(Total) AS MaxTotal, attemptid, userid
  FROM         all_results_by_attemptid
  WHERE     (started < '16 jun 2011')
  GROUP BY userid, attemptid) maxval
on maxval.MaxTotal = all.Total and maxval.userid = all.userid and maxval.attemptid = all.attemptid

Open in new window

There are other ways to select it, like using PARTITION OVER, but those are more complex.
0
 
spanoutAuthor Commented:
Thanks, perfect solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.