selecting most recent record(s)

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

selecting most recent record(s)

Author

Topic

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2009-04-06 : 10:20:33

Running MS SQL 2005. Am only use to the Studio Manager GUI. Have a view which I created from 3 different tables. The following is an extract of the data in the view:

Location Serial Description T_STAMP
81 1081 Bowe 4/6/2009
82 1099 Bowe 310 4/2/2009
83 1092 Bowe Tan 3/11/2009 *
83 1082 Bowe 310S 4/2/2009
84 1084 Arle Tech 3/30/2009
85 1087 Rotary 1 4/6/2009
86 1086 Rotart 2 4/6/2009
86 1085 Arle Tech 3/15/2009 *

The columns are set as: Location (int), Serial(int), Description (varchar(20)), and T_STAMP (time/date)- The time is also included in this field just did not show it in this example.

Anyway what I want to do is generate a query again this view, creating another view that would contain the location, serial, and description where the T_STAMP is most recent. So the output would look like the table above except it would exclude the duplicate records for location 86 and 83. The ones with the * after them.

Any assistance greatly appreciated. Thank you.

Oh and I forgot to mention that sometimes the T_STAMP will have the same date but a more recent time. Just thought I would mention that as well. Thanks again.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-06 : 10:31:07

select t1.* from your_view as t1 inner join
(
select Location, Serial, MAX(T_STAMP) as T_STAMP from your_view
group by Location, Serial
) as t2 on t1.Location=t2.Location and t1.Serial=t2.Serial and t1.T_STAMP=t2.T_STAMP

Madhivanan

Failing to plan is Planning to fail

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2009-04-06 : 11:49:41

Thank you for responding so quickly. Worked like a charm.

quote:
Originally posted by madhivanan

select t1.* from your_view as t1 inner join
(
select Location, Serial, MAX(T_STAMP) as T_STAMP from your_view
group by Location, Serial
) as t2 on t1.Location=t2.Location and t1.Serial=t2.Serial and t1.T_STAMP=t2.T_STAMP

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-07 : 01:49:41

quote:
Originally posted by mrtweaver

Thank you for responding so quickly. Worked like a charm.

quote:
Originally posted by madhivanan

select t1.* from your_view as t1 inner join
(
select Location, Serial, MAX(T_STAMP) as T_STAMP from your_view
group by Location, Serial
) as t2 on t1.Location=t2.Location and t1.Serial=t2.Serial and t1.T_STAMP=t2.T_STAMP

Madhivanan

Failing to plan is Planning to fail

You are welcome

Madhivanan

Failing to plan is Planning to fail

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-04-07 : 03:31:15

quote:
Originally posted by madhivanan

select t1.* from your_view as t1 inner join
(
select Location, Serial, MAX(T_STAMP) as T_STAMP from your_view
group by Location, Serial
) as t2 on t1.Location=t2.Location and t1.Serial=t2.Serial and t1.T_STAMP=t2.T_STAMP

Madhivanan

Failing to plan is Planning to fail

hi madhivanan, will it avoid duplicates?
bcos serial has got distinct values for location 86 and 83.
so query will bring all. am i true?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-07 : 05:22:34

quote:
Originally posted by soorajtnpki

quote:
Originally posted by madhivanan

select t1.* from your_view as t1 inner join
(
select Location, Serial, MAX(T_STAMP) as T_STAMP from your_view
group by Location, Serial
) as t2 on t1.Location=t2.Location and t1.Serial=t2.Serial and t1.T_STAMP=t2.T_STAMP

Madhivanan

Failing to plan is Planning to fail

hi madhivanan, will it avoid duplicates?
bcos serial has got distinct values for location 86 and 83.
so query will bring all. am i true?

Yes it is

It should be

select t1.* from your_view as t1 inner join
(
select Location, MAX(T_STAMP) as T_STAMP from your_view
group by Location
) as t2 on t1.Location=t2.Location and t1.T_STAMP=t2.T_STAMP

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 10:16:59

if sql 2005,


select t1.* 
(
select row_number() over (partition by Location order by T_STAMP desc) as seq,* from your_view
) as t1
where t1.seq>1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-07 : 10:20:09

quote:

Originally posted by visakh16

if sql 2005,


select t1.* 
(
select row_number() over (partition by Location order by T_STAMP desc) as seq,* from your_view
) as t1
where t1.seq>1

t1.seq>1 or t1.seq=1?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 10:36:36

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

if sql 2005,

select t1.*
(
select row_number() over (partition by Location order by T_STAMP desc) as seq,* from your_view
) as t1
where t1.seq>1

t1.seq>1 or t1.seq=1?

Madhivanan

Failing to plan is Planning to fail

yup..that should be t1.seq=1
good spot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources