Group by with MAX and id - 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 with MAX and id

Author

Topic

FrankSausH
Starting Member

10 Posts

Posted - 2010-03-20 : 12:13:20

Hello

I have a table appstat with the fields

[appstat_id] int IDENTITY(1, 1) NOT NULL,
[appstat_prsappid] int NULL,
[appstat_statid] int NULL,
[appstat_dat] datetime NULL

Now i want to get the appstat_id for each appstat_prsappid with the max-date.

May be something like this i found in another post

select city_id, comment_date, comments
from @resident_comment RC
where comment_date = (
select min(comment_date)
from @resident_comment RC1 where RC1.City_id = RC.City_id
)

But i always get 2 rows when there are two entries with the same date for one prsapp_id.

Can anyone show me the way?

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-20 : 12:20:17

[code]
select [appstat_id] ,
[appstat_prsappid] ,
[appstat_statid] ,
[appstat_dat]
from
(
select [appstat_id] ,
[appstat_prsappid] ,
[appstat_statid] ,
[appstat_dat],
ROW_NUMBER() OVER (PARTITION BY [appstat_prsappid] ORDER BY [appstat_dat] DESC) AS Seq
FROM appstat
)t
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

FrankSausH
Starting Member

10 Posts

Posted - 2010-03-20 : 12:30:21

THANK YOU!!

(And only for learning, is there a way to do it with MAX?)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-20 : 12:32:36

Nope. If you've duplicates records for same appstat_prsappid group with same appstat_dat date

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

FrankSausH
Starting Member

10 Posts

Posted - 2010-03-20 : 13:02:39

OK... let me say: you are a very good sql-man :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-20 : 13:19:59

Frank S aus H
wofür steht H?

No, you're never too old to Yak'n'Roll if you're too young to die.

FrankSausH
Starting Member

10 Posts

Posted - 2010-03-20 : 13:26:09

H spielt morgen gegen Schalke04

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-20 : 13:33:30

Allns klor

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-21 : 02:10:52

quote:
Originally posted by FrankSausH

OK... let me say: you are a very good sql-man :)

thanks

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources