February 20, 2012 at 8:55 am
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!
February 20, 2012 at 8:57 am
Please provide the full DDL and sample data along with your expceted results so we can assit you better.
Please see the first link in my signature for more info.
February 20, 2012 at 9:15 am
I have executed the following insert statements:
insert into Cars (idCar,name) values ('1','Audi')
insert into Cars (idCar,name) values ('2','Peugeot')
insert into Cars (idCar,name) values ('3','Mercedes')
insert into Rent (idRent ,idCar,name,date) values ('1','1','Dan','02/11/2009')
insert into comenzi (idRent ,idCar,name,date) values ('2','1','Dan','11/12/2010')
insert into comenzi (idRent ,idCar,name,date) values ('3','2','Todd','10/02/2008')
insert into comenzi (idRent ,idCar,name,date) values ('4','3','Josh','02/03/2011')
insert into comenzi (idRent ,idCar,name,date) values ('5','1','Drake','10/10/2010')
Executing the select statement provided above the result will be:
Audi Dan 3 11/12/2010
Audi Drake 1 10/10/2010
Peugeot Todd 1 10/02/2008
Mercedes Josh 1 02/03/2011
But that is incorrect. The results should be differente in the following way:
Audi shoul only appear twice if Drake rented it as many times as Dan (3 being the maximum times it was rented). Also, the date should be the last date it was ever rented by anyone, not bu the current person(Drake in this case). Hope thats good enough...
comenzi = Rent. sorry i had to translate it
February 20, 2012 at 12:25 pm
This is relatively simple but without ddl (create table scripts) and some sample data (insert statements) it is not likely you will get much help. Please provide as already requested.
If I get bored and happen to feel charitable I may find some time to put this together later this week. If you post this in a readily consumable format like I described you will probably receive an answer within a few minutes.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 20, 2012 at 12:41 pm
Sean Lange (2/20/2012)
This is relatively simple but without ddl (create table scripts) and some sample data (insert statements) it is not likely you will get much help. Please provide as already requested.If I get bored and happen to feel charitable I may find some time to put this together later this week. If you post this in a readily consumable format like I described you will probably receive an answer within a few minutes.
I felt charitable 🙂
CREATE TABLE #cars (idCar int, name varchar(25))
CREATE TABLE #rent (idRent int, idCar int, name varchar(25), [date] date)
GO
insert into #cars (idCar,name) values ('1','Audi')
insert into #cars (idCar,name) values ('2','Peugeot')
insert into #cars (idCar,name) values ('3','Mercedes')
insert into #rent (idRent ,idCar,name,date) values ('1','1','Dan','02/11/2009')
insert into #rent (idRent ,idCar,name,date) values ('2','1','Dan','11/12/2010')
insert into #rent (idRent ,idCar,name,date) values ('3','2','Todd','10/02/2008')
insert into #rent (idRent ,idCar,name,date) values ('4','3','Josh','02/03/2011')
insert into #rent (idRent ,idCar,name,date) values ('5','1','Drake','10/10/2010')
--DROP TABLE #cars
--DROP TABLE #rent
So what exactly do you want the results from this to look like OP? Not what they DO look like, what SHOULD they look like?
Jared
CE - Microsoft
February 20, 2012 at 12:48 pm
SQLKnowItAll (2/20/2012)
I felt charitable 🙂
Cool then maybe I can now feel like not being a snarly ogre. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 20, 2012 at 12:53 pm
I think the OP is looking for something like this:
select * from
(
select c.name as CarName, r.*, row_Number() over(partition by c.idCar order by date desc) as RowNum
from #cars c
join #rent r on c.idCar = r.idCar
)x
where x.RowNum = 1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply