April 12, 2011 at 10:10 am
I am struggling to get my head around what I think should be straight forward. Can someone point out the obvious to me!
I have a table which contains around 300 car registration numbers and another table that contains dates when these cars were rented.
I would like select statement which shows for each car, the latest date it was rented. So I can see which ones have not been used in a while...
Sample data....
create table #cars
(
carReg varchar(10) primary key
)
go
insert into #cars(carReg)
select 'Car1' UNION ALL
select 'Car2' UNION ALL
select 'Car3' UNION ALL
select 'Car4' UNION ALL
select 'Car5';
--==============
create table #carRentals
(
orderNo int primary key,
CarReg varchar(10),
orderDate datetime
)
go
insert into #carRentals(orderNo, CarReg, orderDate)
select 1,'Car1','2011-04-08' UNION ALL
select 2,'Car2','2011-04-06' UNION ALL
select 3,'Car3','2011-04-01' UNION ALL
select 4,'Car4','2011-04-10' UNION ALL
select 5,'Car1','2011-04-07' ;
select * from #cars;
select * from #carRentals;
DROP TABLE #cars;
DROP TABLE #carRentals;
Output should be.
carReg, orderNo, orderDate
But only showing the NEWEST rental based on order date....
As I say, it appears easy but I cannot get my head around it.
April 12, 2011 at 10:34 am
Here is one way to do it
select t.CarReg,t.orderNo,t.orderDate
from
(
select CarReg,orderNo,orderDate,row_number() OVER(PARTITION BY CarReg ORDER BY orderDate desc) as rowID
from #carRentals
)t
where t.rowID=1
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 12, 2011 at 10:43 am
Cheers Dan. It works a treat.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply