Need help with a SQL select statement

  • 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!

  • 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.

  • 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

  • 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/

  • 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

  • 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/

  • 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