-
Mar 6th, 2008, 06:37 AM
#1
Thread Starter
Lively Member
[RESOLVED] SQL query help: how to find 'last' record in a set of duplicates
Hi all,
I have two tables where I need to do a join (customers and transactions), and find the last instance of a transaction that each customer has executed.
the tables essentially look like this:
Customers:
CustomerNumber----FirstName-----LastName----...other info, etc...
1-------------------Bob----------Smith--------...
Transactions
TransactionNumber ---- TransactionDate-----CustomerNumber---- other..etc
1----------------------3/4/08---------------1
2----------------------2/4/08---------------1
The issue is this: I need to find just the last transaction that has been executed by each customer
IN the above DB, those are 2 transactions done by "Bob Smith". Currently, I am using the following sql statement:
Code:
wineDB.Open "SELECT customers.CustomerNumber, transactions.CustomerNumber, customers.firstname, customers.lastname, TransactionDate FROM customers, transactions WHERE transactions.CustomerNumber = customers.CustomerNumber GROUP BY customers.customernumber, transactions.CustomerNumber, customers.firstname, customers.lastname, TransactionDate ORDER BY TransactionDate DESC;", connection, adOpenKeyset, adLockReadOnly, adCmdText
but all it does is order by the descending transaction date, listing all the data from each customer and transaction. So for the above data, the output would be:
Bob Smith, Transaction #1
Bob Smith, Transaction #2
but I only want Bob with Trans #1 as the output.
Suggestions?
-
Mar 6th, 2008, 09:50 AM
#2
Re: SQL query help: how to find 'last' record in a set of duplicates
Alias the tables and then do a correlated subquery on the transactions table to pull out the max date for each customer:
Code:
SELECT customers.CustomerNumber, transactions.CustomerNumber, customers.firstname, customers.lastname, TransactionDate
FROM customers cust, transactions trans
WHERE transactions.CustomerNumber = customers.CustomerNumber
AND trans.TransactionDate = (SELECT MAX(TransactionDate) FROM transactions WHERE CustomerNumber = trans.CustomerNumber)
GROUP BY customers.customernumber, transactions.CustomerNumber, customers.firstname, customers.lastname, TransactionDate
-
Mar 6th, 2008, 10:31 AM
#3
Lively Member
Re: SQL query help: how to find 'last' record in a set of duplicates
The following code would be sufficient
Code:
wineDB.Open "SELECT Top 1 customers.CustomerNumber, transactions.CustomerNumber, customers.firstname, customers.lastname, TransactionDate FROM customers, transactions WHERE transactions.CustomerNumber = customers.CustomerNumber ORDER BY TransactionDate DESC,TransactionNo Desc;", connection, adOpenKeyset, adLockReadOnly, adCmdText
I really dont understand y people use Group By when there is no aggregate function in the query.
Encourage the fellow member’s efforts by rating
- ComIT Solutions
-
Mar 6th, 2008, 11:36 AM
#4
Re: SQL query help: how to find 'last' record in a set of duplicates
I hate to suggest SELECT DISTINCT(customers.CustomerNumber)..
-
Mar 6th, 2008, 10:20 PM
#5
Lively Member
Re: SQL query help: how to find 'last' record in a set of duplicates
Originally Posted by MaximilianMayrhofer
I hate to suggest SELECT DISTINCT(customers.CustomerNumber)..
I Strongly Agree
Encourage the fellow member’s efforts by rating
- ComIT Solutions
-
Mar 8th, 2008, 02:23 PM
#6
Re: SQL query help: how to find 'last' record in a set of duplicates
It just's a need for a simple JOIN that uses a sub-query
Something like:
Code:
Select * From Customers CS
Left Join Transactions TS on TS.CustomerNumber=CS.CustomerNumber
and TS.TransactionDate=(Select Max(TS2.TransactionDate)
From Transactions TS2
Where TS2.CustomerNumber=CS.CustomerNumber)
If you have a possibility of two rows with the same TransactionDate for a customer then you need to get a "single transactionnumber" to join to
Code:
Select * From Customers CS
Left Join Transactions TS on TS.TransactionNumber=(Select Top 1 TS2.TransactionNumber
From Transactions TS2
Where TS2.CustomerNumber=CS.CustomerNumber
Order by TS2.TransactionDate Desc)
Please avoid the use of GROUP BY and DISTINCT - it appears you might be using GROUP BY in this case to get rid of phantom duplicates created by weak joins.
Also - stop using this JOIN syntax
FROM customers, transactions
It forces you to put into the WHERE clause a mixture of JOIN criteria and WHERE criteria.
-
Mar 9th, 2008, 04:16 AM
#7
Lively Member
Re: SQL query help: how to find 'last' record in a set of duplicates
Thanx szlamany for sharing very usefull information.
Is there anything wrong in Post #3?
Encourage the fellow member’s efforts by rating
- ComIT Solutions
-
Mar 9th, 2008, 06:10 AM
#8
Re: SQL query help: how to find 'last' record in a set of duplicates
Originally Posted by VB rookie
The issue is this: I need to find just the last transaction that has been executed by each customer
Originally Posted by ComITSolutions
Is there anything wrong in Post #3?
Yes, at first it's too too wide so it's hard to read.
The query in Post#3 returns only the "latest trans date" record(s) of only one customer.
And a side effect of using TOP 1 is it will return 2 (or more) records if those 2 (or more) records have identical values in every fields retuned.)
Hey, where is "VB rookie"? He seems to be disappeared!
I think the OP wants to return the latest transactions of each and every customer.
(Below I use alias names to shorten the query)
Code:
SELECT C.CustomerNumber, C.firstname, C.lastname,
Max(T.TransactionDate) AS LastTransactionDate
FROM customers AS C INNER JOIN transactions AS T
ON C.CustomerNumber = T.CustomerNumber
GROUP BY C.customernumber, C.firstname, C.lastname;
If use LEFT JOIN instead of INNER JOIN, the query also returns all customers who have no transactions.
-
Mar 9th, 2008, 07:42 AM
#9
Re: SQL query help: how to find 'last' record in a set of duplicates
The OP stated a JOIN to the latest transaction - and indicated vaguely that there were several fields in that table to be JOIN'd to.
With that said a MAX() on TRANSACTIONDATE won't suffice in the SELECT list.
Of course we need the OP to clarify that point.
If the OP only wants the "latest" transactiondate then that's exactly what MAX() does.
If they want to join to just one row in the transaction table - one row for each customer - and that row being the "latest" row - then a JOIN with sub-query as I gave examples for is my suggestion for how to accomplish that.
Last edited by szlamany; Mar 9th, 2008 at 08:18 AM.
-
Mar 9th, 2008, 09:46 AM
#10
Lively Member
Re: SQL query help: how to find 'last' record in a set of duplicates
Originally Posted by anhn
Yes, at first it's too too wide so it's hard to read.
The query in Post#3 returns only the "latest trans date" record(s) of only one customer.
And a side effect of using TOP 1 is it will return 2 (or more) records if those 2 (or more) records have identical values in every fields retuned.)
I Agree, I misunderstood the requirement
Encourage the fellow member’s efforts by rating
- ComIT Solutions
-
Mar 10th, 2010, 05:39 AM
#11
New Member
Re: [RESOLVED] SQL query help: how to find 'last' record in a set of duplicates
even m stuck with same kind of problem but i dont want to use subqueries.
can u tel me another way to solve this problem?
if i can do something in inner join condition??
plz reply..its urgent..
-
Mar 10th, 2010, 07:27 AM
#12
Re: [RESOLVED] SQL query help: how to find 'last' record in a set of duplicates
Welcome to the forum!
You have not given us an example of the query you have so suggestions will simply be out mid-air!
If you don't want to use a sub-query then you at least need to make a VIEW that resolves the "single" row of each "duplicate" set of rows. You only return in the VIEW the MAX() or MIN() or whatever method you want to get a single PRIMARY KEY of each group.
Please give us more info.
-
Mar 11th, 2010, 06:39 AM
#13
New Member
Re: [RESOLVED] SQL query help: how to find 'last' record in a set of duplicates
one table is RP with data:
ResourceID Timestamp ResourceType
2345 10/03/10 22:10:33 1
1234 09/03/10 16:10:33 2
7890 13/02/10 13:12:22 1
2345 11/03/10 23:13:43 3
one is view with data:
ID Name
2345 abc
1234 xyz
7890 pqr
2345 rdf
both tables need to be joined on ResourceID=ID
now i need to join these two tables to get the latest timestamp,name and resourcetype record of a each single ID.
cant use subquery....thanx...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|