Link to home
Start Free TrialLog in
Avatar of samtran0331
samtran0331Flag for United States of America

asked on

Get only the last record from each "set", Max?, Distinct?, huh?

Hi Experts!

I have a query that is returning something like this:

Month     Year     UserID     ......
1            2005     1
2            2005     1
3            2005     1
4            2005     1
1            2005     2
2            2005     2
3            2005     2
4            2005     2
1            2005     3
2            2005     3
3            2005     3
4            2005     3

How can I nest or rewrite this query to return this:
Month     Year     UserID     ......
4            2005     1
4            2005     2
4            2005     3


query:
 SELECT DATEPART([month], sp_ProductionByUser.StatementDate) AS [Month], DATEPART([year],
 sp_ProductionByUser.StatementDate) AS [Year], tblProducerType.ProducerTypeID, sp_ProductionByUser.UserID,
 sp_ProductionByUser.FullName, SUM(sp_ProductionByUser.sumProd) AS sumTotalProd,SUM(sp_ProductionByUser.sumComm) AS sumTotalComm,tblUser.defaultPercentage
 FROM sp_ProductionByUser INNER JOIN
 tblUserProducerType ON sp_ProductionByUser.UserID = tblUserProducerType.UserID INNER JOIN
 tblProducerType ON tblUserProducerType.ProducerTypeID = tblProducerType.ProducerTypeID INNER JOIN
 tblUser ON tblUserProducerType.UserID = tblUser.UserID
 WHERE (sp_ProductionByUser.StatementDate >= '01/01/2004') AND (sp_ProductionByUser.StatementDate <= '03/31/2005')
 GROUP BY sp_ProductionByUser.FullName, DATEPART([month], sp_ProductionByUser.StatementDate), DATEPART([year],
 sp_ProductionByUser.StatementDate), sp_ProductionByUser.UserID, tblProducerType.Q5,
 tblProducerType.ProducerTypeID, tblUser.defaultPercentage
 HAVING (SUM(sp_ProductionByUser.sumProd) <> 0) AND (sp_ProductionByUser.UserID <> 0) AND
 (sp_ProductionByUser.UserID <> 3)
 ORDER BY sp_ProductionByUser.FullName, DATEPART([year], sp_ProductionByUser.StatementDate), DATEPART([month],
 sp_ProductionByUser.StatementDate)



Avatar of Steve Bink
Steve Bink
Flag of United States of America image
Change the sequence of your ORDER BY clause:

ORDER BY DATEPART([month], sp_ProductionByUser.StatementDate), DATEPART([year], sp_ProductionByUser.StatementDate), sp_ProductionByUser.FullName

Avatar of rafrancisco
rafrancisco
Try this:

SELECT DATEPART([month], sp_ProductionByUser.StatementDate) AS [Month], DATEPART([year],
 sp_ProductionByUser.StatementDate) AS [Year], tblProducerType.ProducerTypeID, sp_ProductionByUser.UserID,
 sp_ProductionByUser.FullName, SUM(sp_ProductionByUser.sumProd) AS sumTotalProd,SUM(sp_ProductionByUser.sumComm) AS sumTotalComm,tblUser.defaultPercentage
 FROM sp_ProductionByUser INNER JOIN
 tblUserProducerType ON sp_ProductionByUser.UserID = tblUserProducerType.UserID INNER JOIN
 tblProducerType ON tblUserProducerType.ProducerTypeID = tblProducerType.ProducerTypeID INNER JOIN
 tblUser ON tblUserProducerType.UserID = tblUser.UserID
 WHERE (sp_ProductionByUser.StatementDate >= '01/01/2004') AND (sp_ProductionByUser.StatementDate <= '03/31/2005')

AND
sp_ProductionByUser.UserID = (SELECT MAX(UserID) FROM sp_ProductionByUser B
                                              WHERE B.StatementDate >= '01/01/2004' AND B.StatementDate <= '03/31/2005' AND
                                                         DATEPART([month], sp_ProductionByUser.StatementDate) = DATEPART([month], B.StatementDate) AND
                                                         DATEPART([year], sp_ProductionByUser.StatementDate) = DATEPART([year],
B.StatementDate))


 GROUP BY sp_ProductionByUser.FullName, DATEPART([month], sp_ProductionByUser.StatementDate), DATEPART([year],
 sp_ProductionByUser.StatementDate), sp_ProductionByUser.UserID, tblProducerType.Q5,
 tblProducerType.ProducerTypeID, tblUser.defaultPercentage
 HAVING (SUM(sp_ProductionByUser.sumProd) <> 0) AND (sp_ProductionByUser.UserID <> 0) AND
 (sp_ProductionByUser.UserID <> 3)
 ORDER BY sp_ProductionByUser.FullName, DATEPART([year], sp_ProductionByUser.StatementDate), DATEPART([month],
 sp_ProductionByUser.StatementDate)
Avatar of samtran0331
samtran0331
Flag of United States of America image

ASKER

Thanks routinet...that does put it in that order...but I guess I wasn't clear on the result I need...

It needs to return *only* the highest month/year for each user....

So in my lil example above,
Month     Year     UserID     ......
4            2005     1
4            2005     2
4            2005     3

4/2005 would be the only combo returned for each userID, no other months or years
Avatar of samtran0331
samtran0331
Flag of United States of America image

ASKER

rafrancisco,
That query took almost 2 seconds to run and returned me a really odd dataset:

Month     Year     UserID     ......
3            2005     1
9            2005     1
11          2005     1
1            2005     6
2            2005     6
3            2005     6
1            2005     10
2            2005     10
3            2005     10
4            2005     10
5            2005     10
6            2005     10
ASKER CERTIFIED SOLUTION
Avatar of rafrancisco
rafrancisco
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of samtran0331
samtran0331
Flag of United States of America image

ASKER

rafrancisco,

PURE GENIUS!