SQHell Max Aggregated Nested? - not even sure.

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)

SQHell Max Aggregated Nested? - not even sure.

Author

Topic

ssutter
Starting Member

2 Posts

Posted - 2009-01-19 : 23:21:45

ok seems like a stupid question, but i've tried everything I can think of.

simple table - list of transaction. - fields: UserID, modifieddate, description.

I want to produce a list of unique users, with their most recent transaction (modified date) and the descripion of the latest transaction.

I'm trying it as an aggregate query- (max(modifieddate) but if I do a group by userid, i can't seem to get the description.

How can I do an aggregate query that sends back the fields in the same record as the latest modifieddate without grouping by? I'm getting desperate.

Thanks, I appreciate any help

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-19 : 23:31:39

Try this

select a.userid,a.maxdate,b.description from
(select userid,max(modifieddate) as maxdate from tablename group by userid) a
cross apply
(select description from tablename where userid = a.userid and modifieddate = a.maxdate) b

Jai Krishna

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-19 : 23:51:54

Or

select top 1 with ties userid,modifieddate,description from tblname order by row_number() over(partition by userid order by modifieddate desc)

Jai Krishna

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-20 : 00:11:20

or

select b.userid,b.modifieddate,b.description from
(select *,row_number() over(partition by userid order by modifieddate desc) as seq from urtbl) b
where b.seq =1

Jai Krishna

ssutter
Starting Member

2 Posts

Posted - 2009-01-20 : 11:21:48

Jai! Brilliant - thank you so much, part of my problem ended up being the visual studio interface... ok doing great so far...

so i had this query mostly written by you... (the names are different, it's a sheep metaphor instead of users - sheep_events are transactions)

ok - so here the sql - today's problem is that while I have a sheep_id in the event table, i don't have sheep name or data... how do I do a join on sheep_id to tblsheep? i keep having problems>

select *, b.sheep_id, b.modified, b.contactfreq from
(select *,row_number() over(partition by sheep_id order by modified desc) as seq from tblsheep_event) b
where b.seq =1

i'm trying something like -

SELECT tblSheep.*, tblSheep_Event.*
FROM tblSheep INNER JOIN
(select *, b.sheep_id, b.modified, b.contactfreq from
(select *,row_number() over(partition by sheep_id order by modified desc) as seq from tblsheep_event) b
where b.seq =1)
ON tblSheep.sheep_id = tblSheep_Event.sheep_id

is this the right approach for a join?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources