Group By SQL Statesment - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group By SQL Statesment

Author

Topic

wisdomt
Starting Member

5 Posts

Posted - 2009-07-01 : 11:56:19

Hi,

I am new to using group by in Select statements.

My sql statement is currently

SELECT SiteID, MAX(POValue) AS POValue
FROM dbo.ARQM_Finance_ClientPOs
WHERE Stage = 'Construction'
GROUP BY SiteID
ORDER BY SiteID

which correctly returns the highest Construction PO Value for each site. What I can't get my head around is now I include the unique ID field (or any other field) associated with the row with the highest PO value for each site.

Hope that makes sense, any help would be greatly welcomed.

Thanks,

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 12:01:16

[code]
SELECT t.*
FROM dbo.ARQM_Finance_ClientPOs t
INNER JOIN (SELECT SiteID, MAX(POValue) AS POValue
FROM dbo.ARQM_Finance_ClientPOs
WHERE Stage = 'Construction'
GROUP BY SiteID)t1
ON t1.SiteID=t.SiteID
AND t1.POValue = t. POValue
ORDER BY t.SiteID
[/code]

wisdomt
Starting Member

5 Posts

Posted - 2009-07-02 : 04:40:38

Thanks Visakhs, that worked spot on.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-02 : 04:55:52

[code]SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY SiteID ORDER BY POValue DESC) AS recID
FROM dbo.ARQM_Finance_ClientPOs
WHERE Stage = 'Construction'
) AS d
WHERE recID = 1
ORDER BY SiteID[/code]

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources