Database Query Question - is this possible?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
SpecialK
Forum Commoner
Posts: 96
Joined: Mon Sep 18, 2006 3:49 pm

Database Query Question - is this possible?

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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
User avatar
SpecialK
Forum Commoner
Posts: 96
Joined: Mon Sep 18, 2006 3:49 pm

Post 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
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

The syntax for multiple order by is as follows..

Code: Select all

ORDER BY fielda ASC, fieldb DESC LIMIT 1
User avatar
SpecialK
Forum Commoner
Posts: 96
Joined: Mon Sep 18, 2006 3:49 pm

Post 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
Post Reply