most recent 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

General SQL Server Forums

New to SQL Server Programming

most recent record

Author

Topic

barflyz
Starting Member

47 Posts

Posted - 2010-04-28 : 15:40:55

I have a table with id, test date and test results

I created a temp table with all results and pulled the most recent as shown below.

--1st most recent a1c value--
select d.pid, d.a1c1_value, d.a1c_date
into dbo.bds_a1c1_test1
from dbo.bds_a1c1 d
group by d.pid, d.a1c1_value, d.a1c_date
having a1c_date = (select max (c.a1c_date)
from dbo.bds_a1c1 c where
d.pid=c.pid)

What I want to do next is the select the next most recent id, date and results so I can update to a second column in my master table. Every time I try the approach below it errors out.

select d.pid, d.a1c1_value, d.a1c_date
from dbo.bds_a1c1 d
group by d.pid, d.a1c1_value, d.a1c_date
having d.a1c_date =
(select max(d.a1c_date) as a1c_date
from dbo.bds_a1c1 d
group by d.pid, d.a1c_date
having a1c_date < (select max (c.a1c_date)
from dbo.bds_a1c1_test1 c where
d.pid=c.pid))

error
Error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (State:21000, Native Code: 200)

barflyz
Starting Member

47 Posts

Posted - 2010-04-28 : 15:41:46

when I pull the 2nd most recent it has to be a unqiue row based on id

Sachin.Nand

2937 Posts

Posted - 2010-04-28 : 15:48:37

Try this


select d.pid, d.a1c1_value, d.a1c_date
from dbo.bds_a1c1 d
group by d.pid, d.a1c1_value, d.a1c_date
having d.a1c_date =
(select max(d.a1c_date) as a1c_date
from dbo.bds_a1c1 d1 where d.pid=d1.pid
group by d.pid, d.a1c_date
having a1c_date < (select max (c.a1c_date)
from dbo.bds_a1c1_test1 c where
d1.pid=c.pid))

PBUH

barflyz
Starting Member

47 Posts

Posted - 2010-04-28 : 15:54:35

I get this error

Error: Each GROUP BY expression must contain at least one column that is not an outer reference. (State:37000, Native Code: A4)

barflyz
Starting Member

47 Posts

Posted - 2010-04-28 : 15:57:38

I changed all references to d1 and get this error

Error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (State:21000, Native Code: 200)

Sachin.Nand

2937 Posts

Posted - 2010-04-28 : 16:03:31

quote:
Originally posted by barflyz

I get this error

Error: Each GROUP BY expression must contain at least one column that is not an outer reference. (State:37000, Native Code: A4)

Sorry I think this should work


select d.pid, d.a1c1_value, d.a1c_date
from dbo.bds_a1c1 d
group by d.pid, d.a1c1_value, d.a1c_date
having d.a1c_date =
(select max(d1.a1c_date) as a1c_date
from dbo.bds_a1c1 d1 where d.pid=d1.pid
group by d1.pid, d1.a1c_date
having a1c_date < (select max (c.a1c_date)
from dbo.bds_a1c1_test1 c where
d1.pid=c.pid))

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources