Select Statement - 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

Select Statement

Author

Topic

MirceaM91
Starting Member

8 Posts

Posted - 2012-02-20 : 07:34:43

Hello

Please help me with a difficult(for me at leat) select statement.
So i have the two following tables:
Cars(idCar, name)
Rent (idRent, idCar, name, date)
IdCar and idRent are primary keys. date is of type DATETIME.

I have to write a select statement that will print for all cars the name of the person/persons who has rented it the most times, the maximum times that particular car was rented and the last time it was rented.

I've managed to come up with a select statement that prints the last time each car was rented, the maximum times it was rented but it keeps printing every person it was rented by, even though they dont have the same number of rents for that car. Heres the code:

select c.name, max(r1.date) as 'Last date', r1.name,count(r1.name) as 'Maximum times'
from Cars c join Rents r1 on c.idCar=r1.idCar
group by c.name,r1.name

thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 11:22:50

[code]
select CarName,Renter,RentCnt,LatestRentDate
from
(
select c.name as CarName,r.Name as Renter,cnt as RentCnt,latest as latestRentdate,
row_number() over (partition by c.name order by RentCnt desc) as Rn
from Cars c
inner join (select idCar,name,count(*) as cnt,max(date) as latest
from Rent
group by idCar,name)r
on r.idCar = c.IdCar
)t
where rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MirceaM91
Starting Member

8 Posts

Posted - 2012-02-20 : 12:08:58

Thanks for your reply. I tried running the code you've provided but it doesn't work...it generates an error: Invalid Column Name 'RentCnt'..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 12:10:40

its working fine for me. can you show your used query. I'm sure your used query is different from what I gave you!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MirceaM91
Starting Member

8 Posts

Posted - 2012-02-20 : 12:12:13

Sure. here is my entire code:

create table Cars(idCar varchar(33) primary key, name varchar(33))
create table Rent (idRent varchar (33) primary key, idCar varchar(33) foreign key references Cars(idCar), name varchar (33), date datetime)

insert into Cars (idCar, name ) values ('1','audi')
insert into Cars (idCar, name ) values ('2','mercedes')
insert into Cars (idCar, name ) values ('3','bmw')

insert into Rent (idRent,idCar,name,date) values('1','1','dan','02/03/2008')
insert into Rent (idRent,idCar,name,date) values('2','1','dan','12/03/2008')
insert into Rent (idRent,idCar,name,date) values('3','1','alex','10/03/2009')
insert into Rent (idRent,idCar,name,date) values('4','2','dan','02/03/2008')
insert into Rent (idRent,idCar,name,date) values('5','2','luci','02/03/2008')
insert into Rent (idRent,idCar,name,date) values('6','3','andrei','02/03/2008')

select CarName,Renter,RentCnt,LatestRentDate
from
(
select c.name as CarName,r.Name as Renter,cnt as RentCnt,latest as latestRentdate,
row_number() over (partition by c.name order by RentCnt desc) as Rn
from Cars c
inner join (select idCar,name,count(*) as cnt,max(date) as latest
from Rent
group by idCar,name)r
on r.idCar = c.idCar
)t
where rn=1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 12:19:03

try like this


select CarName,Renter,RentCnt,LatestRentDate
from
(
select c.name as CarName,r.Name as Renter,cnt as RentCnt,latest as latestRentdate,
row_number() over (partition by c.name order by cnt desc) as Rn
from Cars c
inner join (select idCar,name,count(*) as cnt,max(date) as latest
from Rent
group by idCar,name)r
on r.idCar = c.idCar
)t
where rn=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MirceaM91
Starting Member

8 Posts

Posted - 2012-02-20 : 12:25:45

Works like a charm. Thank you very very much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 12:30:47

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources