Real quick question - 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)

Real quick question

Author

Topic

daman
Yak Posting Veteran

72 Posts

Posted - 2007-12-17 : 21:13:27

I want to get one row record (the latest record of an ID) and the ID is from an ID list.

declare @temp table (id int)
insert into @temp
select 1330 union all
select 1336 union all
select 1367

Now i want to run
select top 1 value from Table A, @temp T where A.id = T.id

This will get me 1 value back. I think I need to use
A.id in T.id but it will give me syntax error

I want to get one row for each Id in the @temp table

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-17 : 21:22:02

I am not sure why you need the temp table..

You can just pull the records using an aggregate query...what is the structure of the source table (Table A?)

Poor planning on your part does not constitute an emergency on my part.

daman
Yak Posting Veteran

72 Posts

Posted - 2007-12-17 : 21:33:57

thanks for the quick reply.

Table A structure is

Id Value Time

An id may have different values and I want to get the latest value (max time)
Since table A has many id and i only want to get a subset of that id, one per id.

Thanks and looking forward to your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-18 : 00:24:15

SELECT t.Value FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t1.Time DESC) AS 'RowNo',
t1.Value
FROM TableA t1
INNER JOIN @temp t2
ON t1.Id=t2.Id) t
WHERE t.RowNo=1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-18 : 03:13:24

and see what you can do with Row_number function
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-18 : 03:29:09

select top 1 a.value from TableA as a
inner join @temp as T on A.id = T.id
order by a.value desc

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-18 : 03:38:42

quote:
Originally posted by Peso

select top 1 a.value from TableA as a
inner join @temp as T on A.id = T.id
order by a.value desc

E 12°55'05.25"
N 56°04'39.16"

or
select max(a.value) as value from TableA as a
inner join @temp as T on A.id = T.id

Madhivanan

Failing to plan is Planning to fail

daman
Yak Posting Veteran

72 Posts

Posted - 2007-12-18 : 09:57:06

quote:
Originally posted by visakh16

SELECT t.Value FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t1.Time DESC) AS 'RowNo',
t1.Value
FROM TableA t1
INNER JOIN @temp t2
ON t1.Id=t2.Id) t
WHERE t.RowNo=1

This works great. Thanks a million.

I just found out that I need to get this working on both SQL 2005 and 2003.

Do you know the equivalent of this in 2003 since it has no ROW_NUMBER

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-18 : 10:01:04

select A.id,max(a.value) as value from TableA as a
inner join @temp as T on A.id = T.id
Group by A.id

or

Can you post some sample data with expected result?

Madhivanan

Failing to plan is Planning to fail

daman
Yak Posting Veteran

72 Posts

Posted - 2007-12-18 : 10:13:32

Madhivanan,

Your is very close to what I want...I want to use the Max() to filter out but I don't want to select it

select A.id,max(a.Time) as latest, a.value
from Table A as a
inner join @temp as T on A.id = T.id
Group by A.id, A.value

Here is the sample result

1861 1900-01-01 13:57:00 51
2131 1900-01-01 11:36:00 54.25
2456 1900-01-01 11:36:00 54.25
2650 1900-01-01 13:31:00 55
1420 1900-01-01 14:14:00 56
1367 1900-01-01 14:15:00 57
2812 1900-01-01 14:14:00 59.5

I like to display only the id and the value column only...

THANKS

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-18 : 10:18:10

So just do:


select latest, value
from (
select A.id,max(a.Time) as latest, a.value
from Table A as a
inner join @temp as T on A.id = T.id
Group by A.id, A.value) a

daman
Yak Posting Veteran

72 Posts

Posted - 2007-12-18 : 10:43:39

guys,

Only the one using ROW_NUMBER return one per Id.

The other suggestions return multiple row per id.

1330 63 1900-01-01 08:11:00
1330 64 1900-01-01 09:29:00
1330 64.5 1900-01-01 11:18:00
1330 65 1900-01-01 09:31:00
1330 67 1900-01-01 11:23:00
1336 55.25 1900-01-01 08:15:00
1336 56.25 1900-01-01 08:10:00
1336 56.5 1900-01-01 09:47:00
1336 57 1900-01-01 16:02:00
1336 57.5 1900-01-01 10:41:00
1336 58 1900-01-01 08:36:00

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-18 : 11:01:08

using the data from above:


declare @temp table (id int, value decimal(15,2), Time datetime)

insert into @temp
select 1330, 63, '1900-01-01 08:11:00'
union select 1330, 64, '1900-01-01 09:29:00'
union select 1330, 64.5, '1900-01-01 11:18:00'
union select 1330, 65, '1900-01-01 09:31:00'
union select 1330, 67, '1900-01-01 11:23:00'
union select 1336, 55.25, '1900-01-01 08:15:00'
union select 1336, 56.25, '1900-01-01 08:10:00'
union select 1336, 56.5, '1900-01-01 09:47:00'
union select 1336, 57, '1900-01-01 16:02:00'
union select 1336, 57.5, '1900-01-01 10:41:00'
union select 1336, 58, '1900-01-01 08:36:00'


select a.latest, b.value
from (
select id,max(Time) as latest
from @temp as a
Group by id ) a
inner join @temp b
on a.id = b.id
and a.latest = b.time

daman
Yak Posting Veteran

72 Posts

Posted - 2007-12-18 : 11:27:55

RICKDDDDDD

We are almost there...there is an instance where an ID has exactly the 2 same MAX(Time)...this result in duplicate.

1367 51 1900-01-01 16:00:00.000
1367 52 1900-01-01 16:00:00.000

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-18 : 11:30:53

How would you know which value you wanted in this situation?

daman
Yak Posting Veteran

72 Posts

Posted - 2007-12-18 : 15:33:19

quote:
Originally posted by RickD

How would you know which value you wanted in this situation?

RickD,

Assume we have another column say like TimeId. If an id have 2 of the same time, then we select the one with larger TimeId.

Thanks

daman
Yak Posting Veteran

72 Posts

Posted - 2007-12-18 : 16:56:07

HERE is the table. I hope someone can help. The number I like to get out is
1330 71.25 14942818 1900-01-01 11:23:00
1460 86 15020856 1900-01-01 12:55:00

From the data below

ID Value TimeID Time

1330 71.25 14942818 1900-01-01 11:23:00
1330 68.75 14942692 1900-01-01 11:18:00
1330 71.25 14942536 1900-01-01 11:14:00
1330 70 14940174 1900-01-01 09:31:00
1330 68.75 14940044 1900-01-01 09:29:00
1330 67.5 14937387 1900-01-01 08:11:00
1330 66.5 15085262 1900-01-01 08:11:00
1330 67.5 14937127 1900-01-01 08:10:00
1460 85.5 15020088 1900-01-01 12:55:00
1460 86 15020856 1900-01-01 12:55:00
1460 81 15015558 1900-01-01 11:32:00
1460 85.5 15014212 1900-01-01 09:55:00
1460 81 15013658 1900-01-01 09:41:00
1460 81 15013363 1900-01-01 09:39:00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-18 : 23:57:35

SELECT r.ID,r.Value
FROM TableA r
INNER JOIN 
(
   SELECT t.ID,MAX(t.TimeID) AS 'MaxTimeID' FROM
   TableA t
   INNER JOIN
   (  SELECT ID,MAX(Time) AS 'MaxTime'
      FROM TableA
      GROUP BY ID
   )tmp
   ON tmp.ID=t.ID
   AND tmp.MaxTime=t.Time
   GROUP BY t.ID
)tmax
ON tmax.ID=r.ID
AND tmax.MaxTimeID=r.TimeID

daman
Yak Posting Veteran

72 Posts

Posted - 2007-12-20 : 08:45:51

That's it, Visakh16

Now, a variation of this.

Instead of the Time column, we have DateTime column which include date and time.
2007-12-12 11:23:00
2007-12-12 13:33:20

Now, how would we go about select one per ID for a specific date, say 2007-12-12...if there is no value for that specific date, then find the last date prior to 2007-12-12

And for a certain date, an id has many values, we choose the one with the max(TimeID)

You guys have been phenomenal.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources