Page 1 of 1

Database Query Question - is this possible?

Posted: Fri May 18, 2007 11:06 am
by SpecialK
I have a database that wasn't set up by me, but I have to use.

The premise of the fields are as follows

ID (Primary)
Date
FK_ID
Owing

Now there can have many different rows with the same FK_ID if they have made multiple payments to it. The date is when the payment has been made and it will by reflected in the owing column.

example

1 - 2007/05/05 - 456 - $60.00
2 - 2007/05/09 - 456 - $50.00
3 - 2007/05/12 - 456 - $15.15

When searching this, a join is made with the FK_ID and what I need to retrieve the last row only (I need to get the owing amount). Is there some SQL command that I am not recalling to do this? It seems almost like a max, but is it used that way?

I know I "could" get all rows and then use PHP to compare and get the last result, but this is a trivial example. I'd rather have a query that does this instead of a possibly huge dataset that I would have to parse through for one value.

Posted: Fri May 18, 2007 11:59 am
by Benjamin
Wouldn't the "last" row be the row with the latest date or largest id?

Code: Select all

ORDER BY date DESC LIMIT 1

Code: Select all

ORDER BY id DESC LIMIT 1

Posted: Fri May 18, 2007 1:01 pm
by SpecialK
The order by would work if it was just that table.

I am already requiring to order by something else that is joined to it.

I figure I must do something like

SELECT A,B,C,D,FK_ID,ID,Owing
FROM table1,table2
WHERE A=B and C=FK_ID
ORDER By D

So if I was to do something like that, would it be an "IN" command? Something along the lines of this:

SELECT A,B,C,D,FK_ID,ID,Owing
FROM table1,table2
WHERE A=B and C IN (SELECT FK_ID,Owing WHERE C=FK_ID ORDER BY Owing DESC Limit 1)
ORDER By D

Posted: Fri May 18, 2007 1:16 pm
by Benjamin
The syntax for multiple order by is as follows..

Code: Select all

ORDER BY fielda ASC, fieldb DESC LIMIT 1

Posted: Fri May 18, 2007 2:09 pm
by SpecialK
The database I am using is DB2, which doesn't have the option for limit.

However it does have the option FETCH FIRST

The problem with doing this, it's its not tied together with ORDER BY, but a clause on its own.

Doing

Code: Select all

ORDER BY fielda ASC, fieldb DESC FETCH FIRST 1 ROWS ONLY
Only returns the first row overall, not the first of fieldb