I'm struggling with SQL aggregate functions and find it difficult to find any use for them as the rules are so strict. Can anybody tell me why the first query below doesn't work, and why the second query gives the same rows, just in a different order? What I'm trying to do is find the date and location of the top value for the BORROWER_ID of 10.
For the first query I would expect just a single row to be returned. This is running on Sybase but I assume other versions of SQL would be similar.
The problem as I see it is there's no simple way of telling SQL that we want to return various columns of a row for a single maximum column amongst them.
1> select CREATE_LOCATION, max(CREATE_DATE) from LOAN where BORROWER_ID=10 group by CREATE_DATE
2> go
CREATE_LOCATION
--------------- -------------------------------
SSU Jun 18 2001 9:28AM
SSU Feb 12 2001 11:49AM
SSU Sep 10 2000 10:43AM
SSU Sep 10 2000 10:43AM
SSU Sep 10 2000 10:43AM
SSU Sep 10 2000 10:43AM
SSU Sep 10 2000 10:43AM
(7 rows affected)
1> select CREATE_LOCATION, CREATE_DATE from LOAN where BORROWER_ID=10
2> go
CREATE_LOCATION CREATE_DATE
--------------- -------------------------------
SSU Sep 10 2000 10:43AM
SSU Sep 10 2000 10:43AM
SSU Sep 10 2000 10:43AM
SSU Sep 10 2000 10:43AM
SSU Sep 10 2000 10:43AM
SSU Jun 18 2001 9:28AM
SSU Feb 12 2001 11:49AM
(7 rows affected)
The problem as I see it is there's no simple way of telling SQL that we want to return various columns of a row for a single maximum column amongst them.
There is a simple way. It just doesn't use aggregation. Remember: The purpose of aggregation is to combine multiple rows generated by the FROM clause into single rows in the result set.
Instead, use order by:
select l.*
from loan l
where borrower_id = 10
order by create_date desc
fetch first 1 row only;
In Sybase, you would use top (1) instead of fetch:
select top (1) l.*
from loan l
where borrower_id = 10
order by create_date desc
fetch first 1 row only;