retriving second record - 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)

retriving second record

Author

Topic

yogaanand
Starting Member

3 Posts

Posted - 2009-02-21 : 00:34:07

hi friends

i have one table with date column. i have one record or more record .. i want to take the second record date ..i wrote the query but its taking time to run..

ex
Table name is 'yoga'

ID DateTime
58381 2008-08-12 15:49:33.560
58381 2008-08-12 16:03:03.747
58381 2008-08-12 18:13:17.747

i wrote query like this

select top 1 datetime from yoga where datetime in
(select top 2 datetime from yoga where id=58381
order by datetime)order by datetime desc

if i run for one id means , it's coming fast but i want add this query ii reports , so its taking time

can any one solution please ..

Thanks
Yogaanand

yogi

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-21 : 00:43:06

try any one of these
declare @tab table (ID int, DateTime datetime)
insert into @tab select
58381, '2008-08-12 15:49:33.560' union all select
58381, '2008-08-12 16:03:03.747' union all select
58381, '2008-08-12 18:13:17.747'

select top 1 * from @tab where DateTime < (select max(DateTime) from @tab) order by datetime desc

select id,datetime from (
select row_number() over(partition by id order by id) as rid , * from @tab
)t where rid = 2

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-21 : 00:44:01

select * from
( select *,row_number() over ( partition by ID order by id ) rn from table ) t
where t.rn = 2

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-21 : 01:01:23

select id,max(datetime) from @temp where datetime <( select max(datetime) from @temp)
group by id

yogaanand
Starting Member

3 Posts

Posted - 2009-02-21 : 01:30:47

thanks

yogi

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-21 : 01:32:18

quote:
Originally posted by yogaanand

thanks

yogi

welcome

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-21 : 01:43:16

quote:
Originally posted by yogaanand

thanks

yogi

Welcome

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-21 : 01:43:45

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx

Madhivanan

Failing to plan is Planning to fail

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-21 : 07:34:10

If you are looking for 2nd max date.

Select ID,[DateTime]
from
(Select Dense_Rank() Over(Partition by ID Order by [Datetime]desc)
as ROWID,* from yourtable)Z
Where Z.ROWID =2

barflyz
Starting Member

47 Posts

Posted - 2010-04-28 : 16:30:42

I have a master table with 1,946 ids and want to add date and value to their respective columns, How can I use this select to update the columns?

Select pid,obsvalue,obsdate
from
(Select Dense_Rank() Over(Partition by pid Order by [obsdate]desc)
as ROWID,* from obs where hdid = 28)Z
Where Z.ROWID =1
and pid in (select pt_pid1 from bds_diabetic_values_tmg)
group by pid,obsvalue,obsdate

thanks

Sachin.Nand

2937 Posts

Posted - 2010-04-28 : 16:48:47

Something like this


Update yourtable set yourtable.obsdate=T.obsdate inner join
(

Select pid,obsvalue,obsdate
from
(Select Dense_Rank() Over(Partition by pid Order by [obsdate]desc)
as ROWID,* from obs where hdid = 28)Z
Where Z.ROWID =1
and pid in (select pt_pid1 from bds_diabetic_values_tmg)
group by pid,obsvalue,obsdate
)T on T.pid=yourtable.pid

PBUH

barflyz
Starting Member

47 Posts

Posted - 2010-04-29 : 08:36:03

Hi sodeep

your example works but I found some exceptions. Sometimes patients have the test on the same day so there are 2 results on same day. so if I do Where Z.ROWID =1 to get the first test I get 2 rows for some ids because they had a test on 9/5/09 for 6.6. and 9/5/09 for 6.3. How to edit code to get both from 9/5/09 to list as most recent and the second one on same day as 2nd most recent using Where Z.ROWID =2
thanks!

barflyz
Starting Member

47 Posts

Posted - 2010-04-29 : 08:45:29

I guess I could use a cursor but I try to avoid them

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources