Need help in Query

Question

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Do you mean you want the most recent date for each ID? If so, then Max(Date) and Group By ID should do what you need.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

raghu-935218 SSC Eights! Points: 903 More actions · Answer 2

I need rest of the columsn too......which are not same ......so i cannot do group by on those coloumns.......

GSquared SSC Guru Points: 260824 More actions · Answer 3

;with CTE (Row, ID, MostRecent, Ben) as

(select row_number() over (partition by ID order by Date desc),

ID,

Date,

Ben)

select ID, MostRecent, Ben

from CTE

where Row = 1;

Try that, see if it works.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

GSquared ... think you left out the FROM table clause

;with CTE (Row, ID, MostRecent, Ben) as

(select row_number() over (partition by ID order by Date desc),

ID,

Date,

Ben FROM yourtablesname)

select ID, MostRecent, Ben

from CTE

where Row = 1;

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 5

Yep. Completely missed that one.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon