• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

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)



0
samtran0331
Asked:
samtran0331
  • 3
  • 2
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

PURE GENIUS!
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now