Help us fix this sql statement.

Question

SELECT DISTINCT u.UserID, UserLastName, UserFirstName, UserName, Notified, 
MAX (CycleNumber) as CycleNumber, (CycleCurrentStep) as CycleCurrentStep, 
MAX (CycleDateReported) as CycleDateReported, 
max (cycleid)
FROM [User] u 
left join Cycle c on (u.UserID = c.UserID) 
join UserDivSection us on (u.UserID = us.UserID 
and us.DivSectionID=26) 
group by u.UserID, UserLastName, UserFirstName, UserName, Notified, c.CycleCurrentStep

UserID  UserLastName    UserFirstName   UserName    Notified    CycleNumber CycleCurrentStep    CycleDateReported   
290 Williams    Craig   [email protected]   1   7   0       208
290 Williams    Craig   [email protected]   1   9   3       210
290 Williams    Craig   [email protected]   1   7   5   1/29/2010 3:06:23 PM    204
290 Williams    Craig   [email protected]   1   8   6   2/1/2010 9:26:40 AM 209

We're trying to return rows with unique non-repeating userids matching with the max cyclenumber for that userid. Right now the query is returning the same userid multiple times for various cyclenumbers. We're not exactly sure how to proceed with the correct query. Any help would be appreciated.

Answer 1

Remove CycleCurrentStep from your group by clause.

Answer 2

You could do it with a subquery in your where clause. Something like:

SELECT u.UserID, UserLastName, UserFirstName, UserName, Notified, 
CycleNumber, CycleCurrentStep, CycleDateReported, cycleid
FROM [User] u 
left join Cycle c on (u.UserID = c.UserID) 
join UserDivSection us on (u.UserID = us.UserID and us.DivSectionID=26) 
where cyclenumber = ( select max(cyclenumber) from cycle where cycle.userid = u.userid)

I'm not sure about the UserDivSection part though.

Answer 3

Ths is probably because you group by on c.CycleCurrentStep. This is an unaggregated column from Cycle - if there happen to be more than one distinct value of CycleCurrentStep for all rows in Cycle corresponding to one row from User, then you will get just as many rows for that user in the result as there are distinct values for CycleCurrentStep

You can remove the CycleCurrentStep column from your GROUP BY list, but then you will have to remove it also from the SELECT list - that's because you cannot at the same time aggregate all corresponding Cycle rows as a group and then pick an unaggreagated value and report it. So if you remove CycleCurrentStep from the GROUP BY you must either remove it from the SELECT list too, or apply a meaningful aggreagate function to it, just like you did with the other columns from Cycle where you applied the MAX() function.

Answer 4

SELECT
    u.UserID, 
    UserLastName, 
    UserFirstName, 
    UserName, 
    Notified, 
    c.CycleNumber, 
    c.CycleCurrentStep, 
    c.CycleDateReported, 
    c.cycleid
FROM
    [User] u 
    INNER JOIN 

    (SELECT 
        u.UserID
        Max(CycleNumber) CycleNumber,
    FROM 
        [User] u 
        left join Cycle c on (u.UserID = c.UserID) 
    GROUP BY
        u.UserID) MaxCycle
    ON u.UserID = MaxCycle.UserId
    INNER JOIN Cycle c
    ON MaxCycle.Cycleumber = c.CycleNumber