Select distinct row with max(datetime) from table

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)

Select distinct row with max(datetime) from table

Author

Topic

2k2er
Starting Member

20 Posts

Posted - 2010-10-06 : 08:57:49

Hi Team,

I was trying to create a query which will fetch me a group of machines throwing some error code. But in one of the table that i use, have multiple entries against each machine. This is a column which gives datetime. I want to get each machines with the max datetime data. Please help me out.

The query is:

select ins.InsStrValue, a.CLient0,a.Active0,stat.Sitecode as 'Site Code', stat.MachineName as 'Site Server',
stat.Win32Error, max(stat.Time) from v_StatusMessage as stat left join v_StatMsgInsStrings as ins on
stat.RecordID = ins.RecordID left join v_StatMsgAttributes as att1 on stat.RecordID = att1.RecordID
inner join v_r_system a on a.Netbios_name0=ins.InsStrValue
where stat.Component = 'SMS_CLIENT_CONFIG_MANAGER' and stat.SiteCode like '%'
and stat.Win32Error = '5'
and a.Client0 is NULL
and a.Netbios_name0 IN (SELECT Netbios_name0
FROM v_r_system
GROUP BY Netbios_name0
HAVING COUNT(*) < 2)
group by ins.InsStrValue, a.CLient0,a.Active0,stat.Sitecode, stat.MachineName,
stat.Win32Error,Stat.Time
order by stat.Time desc

many thanks,
2k2er

2k2er
Starting Member

20 Posts

Posted - 2010-10-06 : 14:24:40

Any help guys? ;-(

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-06 : 15:52:26

Sure, here are many mates and they are willing to help you if only you could give
table structure, sample data, wanted output.

No, you're never too old to Yak'n'Roll if you're too young to die.

2k2er
Starting Member

20 Posts

Posted - 2010-10-07 : 09:06:15

Hi Webfred,

Greetings! (and sorry for not elaborating my request ;-)
when i run above query, the result set looks like this:

BDBOFF6042827LT NULL NULL BMW N2633SMS1 5 10/5/2010
BDBOFF6042827LT NULL NULL BMW N2633SMS1 5 10/5/2010
BDBOFF6042827LT NULL NULL BMW N2633SMS1 5 10/4/2010
BDBOFF6042827LT NULL NULL BMW N2633SMS1 5 10/4/2010
BDBOFF6042827LT NULL NULL BMW N2633SMS1 5 10/4/2010
BDBOFF6042827LT NULL NULL BMW N2633SMS1 5 10/3/2010
BDBOFF6042827LT NULL NULL BMW N2633SMS1 5 10/3/2010
BDBOFF6042827LT NULL NULL BMW N2633SMS1 5 10/3/2010
BDBOFF6042827LT NULL NULL BMW N2633SMS1 5 10/2/2010
BDBOFF6042827LT NULL NULL BMW N2633SMS1 5 10/2/2010
BDBOFF6042827LT NULL NULL BMW N2633SMS1 5 10/1/2010
BDBOFF6042827LT NULL NULL BMW N2633SMS1 5 10/1/2010
BDBOFF6042827LT NULL NULL BMW N2633SMS1 5 9/30/2010
BDBOFF6042827LT NULL NULL BMW N2633SMS1 5 9/30/2010
BDBOFF6042827LT NULL NULL BMW N2633SMS1 5 9/29/2010
BDBOFF6042827LT NULL NULL BMW N2633SMS1 5 9/29/2010
BDBOFF6042827LT NULL NULL BMW N2633SMS1 5 9/28/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/24/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/24/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/23/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/23/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/22/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/22/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/21/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/21/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/20/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/20/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/19/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/19/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/18/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/18/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/17/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/16/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/15/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/15/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/14/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/14/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/13/2010
BDBSAL6027506LT NULL NULL BMW N2633SMS1 5 9/13/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/12/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/12/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/11/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/11/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/10/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/10/2010
BDBSAL6027506LT NULL NULL BMW N2633SMS1 5 9/9/2010
BDBSAL6027506LT NULL NULL BMW N2633SMS1 5 9/9/2010
BMBCSD2437DT NULL NULL BMW N2633SMS1 5 9/9/2010
BDBSAL6027506LT NULL NULL BMW N2633SMS1 5 9/9/2010
BDBSAL6027506LT NULL NULL BMW N2633SMS1 5 9/8/2010

Here, machine names are repeated because of multiple dates reported against same machine. What i want is, each machine name with the latest (max) date information only (it should be a distinct list of machines with a single date that is the latest and max date)

Hope it is more clear now.

Thanks,
2k2er

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-07 : 13:08:58

Try:
a quick (and maybe dirty) solution to get only distinct machine and latest date:
First: Your posted query needs an alias for the date: max(stat.Time) as max_date

select [Site Server], max(max_date) as latest_date
from
(Here_comes_your_posted_query_but_WITHOUT_ORDER_BY) as dt
group by [Site Server]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-07 : 13:11:06

Maybe you have to replace [Site Server] by the column name that holds the machine name.
Your column names are not very clear to me.

No, you're never too old to Yak'n'Roll if you're too young to die.

2k2er
Starting Member

20 Posts

Posted - 2010-10-08 : 07:06:16

Thanks for the reply but unfortunately it did not work...

So, i have again modified my query and reduced couple of views to avoid confusion..

My query now looks like this..

select ins.InsStrValue,stat.time,stat.Sitecode as 'Site Code', stat.MachineName as 'Site Server',
stat.Win32Error
from v_StatusMessage as stat left join v_StatMsgInsStrings as ins on
stat.RecordID = ins.RecordID
where stat.Component = 'SMS_CLIENT_CONFIG_MANAGER' and stat.SiteCode like '%'
and stat.Win32Error = '5'
order by ins.InsStrValue desc

I have machine name in one view called 'v_StatMsgInsStrings' and date value in the other view called 'v_StatusMessage'. Now i just joined both tables and ran the query..The result comes with multiple dates against each machinenames (Insstrvalue) as expected.

CGY1ZZCMC1 10/3/2010 BMW N2633SMS1 5
CGY1ZZCMC1 10/7/2010 BMW N2633SMS1 5
CGY1ZZCMC1 10/2/2010 BMW N2633SMS1 5
CGY1ZZCMC1 10/2/2010 BMW N2633SMS1 5
CGY1ZZCMC1 10/1/2010 BMW N2633SMS1 5
CGY1ZZCMC1 9/9/2010 BMW N2633SMS1 5
BMWMKT0440241LT 9/16/2010 BMW N2633SMS1 5
BMWMKT0440241LT 9/15/2010 BMW N2633SMS1 5
BMOSAL0122004LT 9/18/2010 BMW N2633SMS1 5
BMOSAL0122004LT 9/11/2010 BMW N2633SMS1 5
BMOSAL0122004LT 9/12/2010 BMW N2633SMS1 5
BMOSAL0122004LT 9/25/2010 BMW N2633SMS1 5
BMOMKTEX57338LT 9/25/2010 BMW N2633SMS1 5
BMOMKTEX57338LT 9/19/2010 BMW N2633SMS1 5
BMOMKTEX57338LT 9/19/2010 BMW N2633SMS1 5
BMOMKTEX57338LT 10/2/2010 BMW N2633SMS1 5
BMOMKTEX57338LT 9/26/2010 BMW N2633SMS1 5
BMOMKTEX57338LT 9/12/2010 BMW N2633SMS1 5
BMOMKTEX57338LT 9/11/2010 BMW N2633SMS1 5
BMOMKTEX57338LT 9/18/2010 BMW N2633SMS1 5
BMOMKTEX57338LT 10/3/2010 BMW N2633SMS1 5
BMMKTONH002265 10/3/2010 BMW N2633SMS1 5
BMMKTONH002265 10/4/2010 BMW N2633SMS1 5
BMMKTONH002265 10/5/2010 BMW N2633SMS1 5
BMMKTONH002265 10/6/2010 BMW N2633SMS1 5
BMMKTONH002265 10/5/2010 BMW N2633SMS1 5
BMMKTONH002265 10/6/2010 BMW N2633SMS1 5
BMMKTONH002265 9/18/2010 BMW N2633SMS1 5
BMMKTONH002265 9/11/2010 BMW N2633SMS1 5
BMMKTONH002265 9/12/2010 BMW N2633SMS1 5

As you can see, there are same machinenames because of multiple dates.

So, in my above query, how to make use of Max function or top 1 (i am not sure) to avoid the duplicates and display only with the max (latest) date.

Thanks again man...

Jaison

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-08 : 07:34:53

[code]select InsStrValue, [time], [Site Code], [Site Server], Win32Error
from
(
select
row_number() over (partition by ins.InsStrValue order by stat.[time] desc) as rownum,
ins.InsStrValue,
stat.[time],
stat.Sitecode as [Site Code],
stat.MachineName as [Site Server],
stat.Win32Error

from v_StatusMessage as stat
left join v_StatMsgInsStrings as ins
on stat.RecordID = ins.RecordID

where stat.Component = 'SMS_CLIENT_CONFIG_MANAGER'
and stat.SiteCode like '%' --what should this do for you?
and stat.Win32Error = '5'
) as dt

order by InsStrValue desc
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

2k2er
Starting Member

20 Posts

Posted - 2010-10-08 : 07:52:26

Hi Man,

The query ran without any error but still i am getting the same result...It returned same count with duplicates as my previous query...

Thanks,
Jaison

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-08 : 08:52:10

quote:

Originally posted by webfred

select InsStrValue, [time], [Site Code], [Site Server], Win32Error
from
(
 select 
 row_number() over (partition by ins.InsStrValue order by stat.[time] desc) as rownum,
 ins.InsStrValue,
 stat.[time],
 stat.Sitecode as [Site Code], 
 stat.MachineName as [Site Server],
 stat.Win32Error

 from v_StatusMessage as stat 
 left join v_StatMsgInsStrings as ins 
    on stat.RecordID = ins.RecordID

 where stat.Component = 'SMS_CLIENT_CONFIG_MANAGER' 
 and stat.SiteCode like '%' --what should this do for you?
 and stat.Win32Error = '5'
) as dt
WHERE rownum=1
order by InsStrValue desc

No, you're never too old to Yak'n'Roll if you're too young to die.

My bad

I've forgotten the red marked part.
Now it should work - sorry.

No, you're never too old to Yak'n'Roll if you're too young to die.

2k2er
Starting Member

20 Posts

Posted - 2010-10-08 : 09:08:34

It worked...and thanks a ton for giving me a chance of learning row number() and the partition magic which will save my life from temp tables and complex queries...(Time to move out from SQL 2000..) ;-)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-08 : 09:44:47

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

2k2er
Starting Member

20 Posts

Posted - 2010-11-04 : 11:09:27

Hi webfred,

I am back...I require another help. I would like to get the same logic in a SQL 2000 environment. Is it possible?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-04 : 12:17:56

It is a bit more complicated.
Same tables and same conditions?

No, you're never too old to Yak'n'Roll if you're too young to die.

2k2er
Starting Member

20 Posts

Posted - 2010-11-04 : 12:43:44

Yes...same...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-04 : 13:00:46

I've had no chance to test it but give it a try:

-- first we use a temp table to get ordered distinct values by using identity
select
 identity(int,1,1) as rownum,
 ins.InsStrValue,
 stat.[time],
 stat.Sitecode as [Site Code], 
 stat.MachineName as [Site Server],
 stat.Win32Error

into #numbered

 from v_StatusMessage as stat 
 left join v_StatMsgInsStrings as ins 
    on stat.RecordID = ins.RecordID

 where stat.Component = 'SMS_CLIENT_CONFIG_MANAGER' 
 and stat.SiteCode like '%' --what should this do for you?
 and stat.Win32Error = '5'
order by ins.InsStrValue,stat.[time] DESC

-- now we have reliable values for our count(*) to get the rownum
select InsStrValue, [time], [Site Code], [Site Server], Win32Error
from
(
 select InsStrValue, [time], [Site Code], [Site Server], Win32Error,
 (select count(*) from #numbered inner_t where inner_t.id = outer_t.id and inner_t.rownum <= outer_t.rownum) as rownum
 from #numbered outer_t
) as dt
where rownum=1
order by InsStrValue desc

-- the select is done and we are cleaning... 
drop table #numbered

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources