GROUP BY question - 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

SQL Server 2005 Forums

Transact-SQL (2005)

GROUP BY question

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-09-18 : 06:18:01

Hi,

Got this statement


SELECT cap.PR_Id, cap.PR_Basic, cap.PR_ModifiedDate, changes.value, changes.datetime FROM NVDPrices AS cap LEFT OUTER JOIN
dbCapHistorical.dbo.tblChanges AS changes ON cap.PR_Id = changes.capid
WHERE (cap.PR_EffectiveTo IS NULL) AND (changes.field = 'PR_Basic') AND (changes.type = 'car') AND changes.capid = 100000

It gives these results

PR_ID PR_Basic PR_ModDate value datetime
100000 120000.00 2008-09-02 120000.00 2008-09-02
100000 120000.00 2008-09-02 100000.00 2008-09-01

I want to group the results and only show the one with the latest datetime - so in this case

100000 120000.00 2008-09-02 120000.00 2008-09-02

I've tried to group by pr_id, pr_basic and pr_moddate and use MAX(datetime) but this sill leaves the value field out - how can I do this?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 06:21:35

[code]SELECT t.PR_Id, t.PR_Basic, t.PR_ModifiedDate, t.value, t.datetime
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY cap.PR_Id ORDER BY changes.datetime DESC) AS Seq,cap.PR_Id, cap.PR_Basic, cap.PR_ModifiedDate, changes.value, changes.datetime FROM NVDPrices AS cap LEFT OUTER JOIN
dbCapHistorical.dbo.tblChanges AS changes ON cap.PR_Id = changes.capid
WHERE (cap.PR_EffectiveTo IS NULL) AND (changes.field = 'PR_Basic') AND (changes.type = 'car') AND changes.capid = 100000
)t
WHERE t.Seq=1[/code]

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-09-18 : 06:23:31

GROUP By should work, is there any columns missing from u r original query to the posted one


SELECT cap.PR_Id, cap.PR_Basic, cap.PR_ModifiedDate, changes.value, MAX(changes.datetime) 'datetime'
FROM NVDPrices AS cap 
LEFT OUTER JOIN dbCapHistorical.dbo.tblChanges AS changes ON cap.PR_Id = changes.capid
WHERE (cap.PR_EffectiveTo IS NULL) 
	AND (changes.field = 'PR_Basic') 
	AND (changes.type = 'car') 
	AND changes.capid = 100000
GROUP BY cap.PR_Id, cap.PR_Basic, cap.PR_ModifiedDate, changes.value

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 06:55:10

quote:

Originally posted by PeterNeo

GROUP By should work, is there any columns missing from u r original query to the posted one


SELECT cap.PR_Id, cap.PR_Basic, cap.PR_ModifiedDate, changes.value, MAX(changes.datetime) 'datetime'
FROM NVDPrices AS cap 
LEFT OUTER JOIN dbCapHistorical.dbo.tblChanges AS changes ON cap.PR_Id = changes.capid
WHERE (cap.PR_EffectiveTo IS NULL) 
	AND (changes.field = 'PR_Basic') 
	AND (changes.type = 'car') 
	AND changes.capid = 100000
GROUP BY cap.PR_Id, cap.PR_Basic, cap.PR_ModifiedDate, changes.value

No use grouping by value as value will be different for each line and again you will get duplicates. Alternatively you could do the below

SELECT cap.PR_Id, cap.PR_Basic, cap.PR_ModifiedDate, changes.value, changes.datetime
FROM NVDPrices AS cap 
LEFT OUTER JOIN dbCapHistorical.dbo.tblChanges AS changes ON cap.PR_Id = changes.capid
LEFT OUTER JOIN 
(SELECT capid,MAX(datetime) AS MaxDate
FROM dbCapHistorical.dbo.tblChanges
GROUP BY capid) changes2 
ON changes2.capid=changes.capid
AND changes2.MaxDate=changes.datetimeWHERE (cap.PR_EffectiveTo IS NULL) 
	AND (changes.field = 'PR_Basic') 
	AND (changes.type = 'car') 
	AND changes.capid = 100000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources