[veronicab]

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

[r937]

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/

[veronicab]

Thank you very much!!!

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

Regards,
Veronica

[r937]

this is a great article --
http://www.bcarter.com/sap29.htm

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


rudy

[karim2k]

great SQL enjoyement !!