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)



LVL 37
samtran0331Asked:
Who is Participating?
 
rafranciscoCommented:
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

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


 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)
0
 
Steve BinkCommented:
Change the sequence of your ORDER BY clause:

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

0
 
rafranciscoCommented:
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)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
samtran0331Author Commented:
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
0
 
samtran0331Author Commented:
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
0
 
samtran0331Author Commented:
rafrancisco,

PURE GENIUS!
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.