Need Help in Filtering - 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

Need Help in Filtering

Author

Topic

sabtheleo
Starting Member

10 Posts

Posted - 2010-11-10 : 20:12:07

I am new to SQL..I need help in filtering the rows..
I have table looks like this

ID BusID Latitude RecordDate
178, bu-21316, 42.3354688, 2010-11-10 20:06:15.000
177, bu-21315, 42.3534176, 2010-11-10 20:06:15.000
176, bu-1111, 42.35128, 2010-11-10 20:06:14.000
175, bu-21315, 42.3534144, 2010-11-10 20:06:06.000
174, bu-1111, 42.35128, 2010-11-10 20:06:14.000
173, bu-21316, 42.3355072, 2010-11-10 20:06:06.000
172, bu-21315, 42.3534144, 2010-11-10 20:06:06.000
171, bu-1111, 42.351136, 2010-11-10 20:06:05.000

I have to filter the repetitions based on recent "RecordDate" field.
I don't want to do any hardcoding. I am trying to do something like comparing the recordDate field among same "busID" rows, the one which has recent date has to be in my final table.

Can anybody help me...

My Result Should Be Like this..

ID BusID Latitude RecordDate
178, bu-21316, 42.3354688, 2010-11-10 20:06:15.000
177, bu-21315, 42.3534176, 2010-11-10 20:06:15.000
176, bu-1111, 42.35128, 2010-11-10 20:06:14.000

Thanks in advance..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-11-10 : 20:54:14

[code]
;with data
as
(
select row_no = row_number() over (partition by ID order by RecordDate desc),
ID, BusID, . . . .
from atable
)
select *
from data
where row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sabtheleo
Starting Member

10 Posts

Posted - 2010-11-10 : 21:08:05

quote:

Originally posted by khtan


;with data
as
(
    select row_no = row_number() over (partition by ID order by RecordDate desc), 
           ID, BusID, . . . .
    from   atable
)
select *
from   data
where  row_no = 1

KH
[spoiler]Time is always against us[/spoiler]

@khtan
Thanks for the reply..

I tried it... it didn't work for me...
it is selecting all the fields and has column "row_no" with value 1

sabtheleo
Starting Member

10 Posts

Posted - 2010-11-10 : 21:10:23

@khtan...

I changed the partition by BusID... it is working now..
thanks for the help..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-11-10 : 21:16:55