Database Query Question - is this possible?
Posted: Fri May 18, 2007 11:06 am
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.
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.