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.
Database Query Question - is this possible?
Moderator: General Moderators
Wouldn't the "last" row be the row with the latest date or largest id?
Code: Select all
ORDER BY date DESC LIMIT 1Code: Select all
ORDER BY id DESC LIMIT 1The 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
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
The syntax for multiple order by is as follows..
Code: Select all
ORDER BY fielda ASC, fieldb DESC LIMIT 1The 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
Only returns the first row overall, not the first of fieldb
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