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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", 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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
jogosCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.