Select !!

Question

Select !!

igngua

SSCrazy

Points: 2510
More actions
April 13, 2010 at 9:48 am

#213794

Hi Guys!
scenario;
col1 col2
ID1 datetime1
ID1 datetime2
ID2 datetime3
ID2 datetime4
I need to get the complete row for the newest datetime for each ID on an table.
Thanks in advance.

Viewing 9 posts - 1 through 8 (of 8 total)

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

ps. One Orange Chip Points: 29255 More actions · Answer 1

igngua (4/13/2010)
Hi Guys!
scenario;
col1 col2
ID1 datetime1
ID1 datetime2
ID2 datetime3
ID2 datetime4
I need to get the complete row for the newest datetime for each ID on an table.
Thanks in advance.

select * from myTable where col2 in (select max(col2) from myTable group by Id)

Do u have any column that uniquely identifies a row? i assumed it to be col2...

Pradeep Singh

igngua SSCrazy Points: 2510 More actions · Answer 2

ps. (4/13/2010)
igngua (4/13/2010)
Hi Guys!
scenario;
col1 col2
ID1 datetime1
ID1 datetime2
ID2 datetime3
ID2 datetime4
I need to get the complete row for the newest datetime for each ID on an table.
Thanks in advance.
select * from myTable where col2 in (select max(col2) from myTable group by Id)
Do u have any column that uniquely identifies a row? i assumed it to be col2...

thank!! it worked fine!!

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 3

Or just a

select top 1

max(date), col1, col2, col3

from mytable

group by col1, col2, col3

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

igngua SSCrazy Points: 2510 More actions · Answer 4

Steve Jones - Editor (4/13/2010)
Or just a
select top 1
max(date), col1, col2, col3
from mytable
group by col1, col2, col3

that´s the first thing a tried but it didn´t work i guess i did something wrong. i´ll try...

igngua SSCrazy Points: 2510 More actions · Answer 5

ps. (4/13/2010)
igngua (4/13/2010)
Hi Guys!
scenario;
col1 col2
ID1 datetime1
ID1 datetime2
ID2 datetime3
ID2 datetime4
I need to get the complete row for the newest datetime for each ID on an table.
Thanks in advance.
select * from myTable where col2 in (select max(col2) from myTable group by Id)
Do u have any column that uniquely identifies a row? i assumed it to be col2...

ups!

it still shows duplicated ID´s...

In this case the identifier is col1

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 6

You should post the code you're running, some sample data, and where the dups are occuring.

I might change the "in" to an = as well

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

igngua SSCrazy Points: 2510 More actions · Answer 7

Steve Jones - Editor (4/13/2010)
You should post the code you're running, some sample data, and where the dups are occuring.
I might change the "in" to an = as well

select *

from curvas

where anaidcur in (select anaid

from analisis

where anaestad0='1') and fecha in (select max(fecha) from curvas group by anaidcur)

order by anaidcur

the results are like...

desc datetime id

Bromuro2007-12-24 14:17:50.000AMINBrSM10200H

Bromuro2008-02-04 15:23:47.000AMINBrSM10200H

ps. One Orange Chip Points: 29255 More actions · Answer 8

Can you post table structure and sample data?

This site will help you in posting the same.

qa.sqlservercentral.com/articles/Best+Practices/61537/

Pradeep Singh