| eliminating rows when more than one rows has been returned [message #907] |
Tue, 12 March 2002 03:56  |
shad
Messages: 50
Registered: October 2000
|
Member |
|
|
Background
I have two tables, transaction and client.
The table transaction has the following fields.
clientref, transactiondate, amount
The table has the following fields:
clientref, surname
There is a many-to-one relation from transaction table to client table. You can have different transactions (recorded on the same dates) for one client. These table are joined by clientref.
The query
Sql> Select t.clientref,c.surname,t.transactiondate,t.amount
From transaction t, client c
Where t.clientref = c.clientref.
Result.
Clientref surname transactiondate amount
12345 robins 21-Jun-2000 250
30912 james 10-Jul-2001 300
12345 robins 10-Sep-2001 50
209178 dave 19-Aug-1999 250
12345 robins 10-Feb-2002 250
Now, the query above has returned 5 rows. But 3 rows refer to client 12345 (robins). I only want to see one payment, say the latest one (10-Feb-2002).
How would I remove other two rows assuming the database is very big so I may not know there are more than one payments for this particular client (meaning I can’t say where
transactiondate = ‘1-Feb-2002’. I must just run a query which will go and find all transactions for each client, count how many they are, and find and return the latest.
I have tried decode,greatest,all,any,exists, group by. None of this constructs seem to be helping me.
Regards
Shad.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
