Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Posts
    12

    Unanswered: Help with a Query!!!

    Hello!

    I am stuck with a query, thank you very much for your help!!!

    I have a table (let's call it booksborrow) like this one:

    create table booksborrow (
    user varchar(20),
    date_borrow int4,
    book varchar(10),
    comments text );

    In the date_borrow field I save the timestamp.

    Is there a way to make one query to get which is the last book every user has borrowed ?

    I would need in the result set, just one row per user, containing the information of the last book they have borrowed (the record with the highest date_borrow for each user).

    I have tried many queries but I just cannot get this one right.

    Thank you very much for your help again!!!

    Regards,
    Veronica
    Last edited by veronicab; 07-15-03 at 00:59.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select user, date_borrow, book, comments
    from booksborrow ZZ
    where date_borrow =
    ( select max(date_borrow)
    from booksborrow
    where user = ZZ.user )

    this is a correlated subquery

    each row in the outer table, the table with the alias ZZ (the correlation variable), is compared to all the other rows in its group

    this group consists of those rows which have the same user


    if you think about it procedurally -- start with the first row in the table, get all the rows that have the same user, sort them by date, take the top 1, get the next row in the table, get all the rows that have the same user, sort them by date, take the top 1, and so on -- then your brain will turn to tapioca

    instead, think of what you want

    the last date for each user

    the users must be grouped

    correlation is one way to do that

    (GROUP BY is another, but in this example the correlated subquery is a lot neater)

    so don't think about it procedurally, think about how to describe the results you want, and let the database figure out how to do it

    rudy
    http://r937.com/

  3. #3
    Join Date
    Dec 2002
    Posts
    12
    Thank you very much!!!

    Could you tell me where I can find more information about correlated sub querys?

    Regards,
    Veronica

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is a great article --
    http://www.bcarter.com/sap29.htm

    you can find more via google, there's lots out there


    rudy

  5. #5
    Join Date
    Apr 2003
    Location
    Tunisia
    Posts
    192

    Thumbs up cool

    great SQL enjoyement !!
    Open up
    Take a look to my Blog http://www.rundom.com/karim2k

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •