Finding the Max value per group.

Clark Kent123

I am having such a hard time writing a SQL query. Can someone please look over this query and see where am I going wrong? I will be using the XML elements to describe the table even though I am using SQL Server 2008 R2..

Here is an example of the data that is listed. ProjectID ReportMonth ReportYear 1000700 05 2008 1000700 06 2008 1000700 07 2008 1000700 12 2008 1000700 01 2009 <-- I want the query to find this! Even though the Month is not the max value, but the Year is the Max value. Here is what I have so far. Where am I going wrong?

SELECT vPSR.ProjectID, vPSR.ReportMonth, vPSR.ReportYear
FROM vPSR
WHERE vPSR.ReportMonth =
(SELECT MAX(ReportMonth)
FROM vPSR AS f
WHERE f.ProjectID = ProjectID
AND ReportYear =
(SELECT MAX(ReportYear)
FROM vPSR AS k
WHERE k.ProjectID = vPSR.ProjectID))
ORDER BY vPSR.ProjectID ASC

I got the idea on how to write the above query here.[^] I don't understand it that well. If you need me to clarify anything please do not hesitate to ask.

scottgp

I'm thinking that ReportMonth and ReportYear should instead have been a ReportDate with some sort of Date datatype. Since they're not, concatenating the two columns seems to work:

SELECT ProjectID, reportMonth, reportYear
FROM test
WHERE (reportYear + reportMonth =
(SELECT MAX(reportYear + reportMonth) AS Expr1
FROM test AS b
WHERE (ProjectID = ProjectID)))

Scott

Corporal Agarn

You could try something like

SELECT vPSR.ProjectID, vPSR.ReportMonth, vPSR.ReportYear
FROM vPSR
INNER JOIN (
SELECT ProjectID, MAX(reportYear + reportMonth) AS Expr1
FROM vPSR
GROUP by ProjectID
) AS v2
ON vPSR.ProjectID = v2.ProjectID
AND (vPSR.ReportYear + vPSR.ReportMonth) = v2.Expr1

Lost User

Can you extend the sample population to give some more detail? In the meantime, here's something to play with;

SET NOCOUNT ON;
BEGIN TRANSACTION

IF OBJECT_ID('vPSR') IS NOT NULL DROP TABLE vPSR

SELECT 1000700 as ProjectID,
05 as ReportMonth,
2008 as ReportYear
INTO vPSR
UNION SELECT 1000700, 06, 2008
UNION SELECT 1000700, 07, 2008
UNION SELECT 1000700, 12, 2008
UNION SELECT 1000700, 01, 2009
UNION SELECT 1000701, 11, 2011
UNION SELECT 1000701, 12, 2009

-- If you insist on using a max
SELECT TOP 1 *
FROM vPSR
WHERE ReportYear = (SELECT MAX(ReportYear) FROM vPSR)
ORDER BY ReportMonth DESC

-- this would do too;
SELECT TOP 1 *
FROM vPSR
ORDER BY ReportYear DESC, ReportMonth DESC

-- per project?
SELECT DISTINCT ProjectID
, (SELECT MAX(ReportYear) FROM vPSR WHERE ProjectID = A.ProjectID)
, (SELECT TOP 1 ReportMonth FROM vPSR WHERE ProjectID = A.ProjectID AND ReportYear = (
SELECT MAX(ReportYear) FROM vPSR WHERE ProjectID = A.ProjectID) ORDER BY ReportMonth DESC)
FROM vPSR A

ROLLBACK

My results;

ProjectID ReportMonth ReportYear

1000701 11 2011

ProjectID ReportMonth ReportYear

1000701 11 2011

ProjectID

1000700 2009 1
1000701 2011 11

Bastard Programmer from Hell :suss:

Clark Kent123

Quote:

Can you extend the sample population to give some more detail?

Yes, I sure can! What I need to do is organize a table that has multiple listings of a Project ID and return only 1 of the latest (date related) per Project ID. Therefore, here is a sample listing of the data that I need to organize.

ProjectID ReportYear ReportMonth

100005000 2008 5
100005000 2008 6
100005000 2008 7
100006600 2008 8
100006600 2008 9
100006600 2008 10
100006600 2008 11
100006600 2008 12
100006600 2009 1
100006800 2008 8
100006800 2008 9
100006800 2009 1
100006800 2009 2
100006800 2009 3
100006800 2009 4

Note: I trimmed down the data so as not to overwhelm this post, but still relay the idea of what I am dealing with. There is also one more thing I would like to ask your advice. It's about an idea that I have been thinking about. What if I were to instead create a temporary table and create an extra column with "ReportDate" and add the Year and Month together. (I know someone has also mentioned this in a reply to my first post). So, it would be something to the affect of (ReportYear * 100) + ReportMonth. Then I could write a query with the above sample data and return back per ProjectID the latests info. What do you think? Further, can you please link me to a "How-To" article on creating temp tables in SQL Server 2008? One last question about something that I don't understand and have been searching MSDN and Google to no avail. It's this whole alias thing ("AS" keyword). You can place use this in for the column name and/or the table name. But I don't understand how I can do something to affect of ...

SELECT MAX(ReportYear) FROM vPSR AS f WHERE f.ProjectID = vPSR.ProjectID

I don't understand how f.ProjectID can equal vPSR.ProjectID. This seems very circular to me. This is a technique that I don't use often. If you have any info/advice about this way of writing a query can you link me? Thanks!

Clark Kent123

I whole heartedly agree about concatenating the ReportMonth + ReportYear columns together. That was another idea that I have been thinking about. Thanks!

Lost User

Clark Kent123 wrote:

What I need to do is organize a table that has multiple listings of a Project ID and return only 1 of the latest (date related) per Project ID.

The last sql-statement is almost a literal translation of that request; it fetches a unique list of all projectid's (distinct), and then the maximum year for that projectid. Lastly, it fetches the maximum month for that projectid in the max(year).

SET NOCOUNT ON;
BEGIN TRANSACTION
SELECT 100005000 as ProjectID,
2008 as ReportYear,
05 as ReportMonth
INTO vPSR
UNION SELECT 100005000, 2008, 6
UNION SELECT 100005000, 2008, 7
UNION SELECT 100006600, 2008, 8
UNION SELECT 100006600, 2008, 9
UNION SELECT 100006600, 2008, 10
UNION SELECT 100006600, 2008, 11
UNION SELECT 100006600, 2008, 12
UNION SELECT 100006600, 2009, 1
UNION SELECT 100006800, 2008, 8
UNION SELECT 100006800, 2008, 9
UNION SELECT 100006800, 2009, 1
UNION SELECT 100006800, 2009, 2
UNION SELECT 100006800, 2009, 3
UNION SELECT 100006800, 2009, 4

SELECT DISTINCT ProjectID
, (SELECT MAX(ReportYear) FROM vPSR WHERE ProjectID = A.ProjectID) AS MaxReportYear
, (SELECT TOP 1 ReportMonth FROM vPSR WHERE ProjectID = A.ProjectID AND ReportYear = (
SELECT MAX(ReportYear) FROM vPSR WHERE ProjectID = A.ProjectID) ORDER BY ReportMonth DESC)
AS MaxReportMonth
FROM vPSR AS A
ROLLBACK

ProjectID MaxReportYear MaxReportMonth

100005000 2008 7
100006600 2009 1
100006800 2009 4

Clark Kent123 wrote:

What if I were to instead create a temporary table and create an extra column with "ReportDate" and add the Year and Month together. (I know someone has also mentioned this in a reply to my first post). So, it would be something to the affect of (ReportYear * 100) + ReportMonth. Then I could write a query with the above sample data and return back per ProjectID the latests info. What do you think? Further, can you please link me to a "How-To" article on creating temp tables in SQL Server 2008?

There's two ways of declaring a temp table in Sql Server, and there's a CodeProject article[

Clark Kent123

Wow. You have given me much food for thought. Let me digest all this info. If I have any questions I will bug you again! :laugh: I really do appreciate the help. :thumbsup:

Lost User

My pleasure, and you're welcome :)