Results 1 to 13 of 13

Thread: [RESOLVED] SQL query help: how to find 'last' record in a set of duplicates

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Nov 2006
    Posts
    105

    Resolved [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?

  2. #2
    Frenzied Member
    Join Date
    May 2006
    Location
    Toronto, ON
    Posts
    1,093

    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

  3. #3
    Lively Member ComITSolutions's Avatar
    Join Date
    Feb 2008
    Location
    Bangalore
    Posts
    94

    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

  4. #4
    Frenzied Member MaximilianMayrhofer's Avatar
    Join Date
    Aug 2007
    Location
    IM IN YR LOOP
    Posts
    2,001

    Re: SQL query help: how to find 'last' record in a set of duplicates

    I hate to suggest SELECT DISTINCT(customers.CustomerNumber)..

  5. #5
    Lively Member ComITSolutions's Avatar
    Join Date
    Feb 2008
    Location
    Bangalore
    Posts
    94

    Re: SQL query help: how to find 'last' record in a set of duplicates

    Quote 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

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  7. #7
    Lively Member ComITSolutions's Avatar
    Join Date
    Feb 2008
    Location
    Bangalore
    Posts
    94

    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

  8. #8
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: SQL query help: how to find 'last' record in a set of duplicates

    Quote Originally Posted by VB rookie
    The issue is this: I need to find just the last transaction that has been executed by each customer
    Quote 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.
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    • Baby Steps a guided tour
    • IsDigits() and IsNumber() functions • Wichmann-Hill Random() function • >> and << functions for VB • CopyFileByChunk

  9. #9
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  10. #10
    Lively Member ComITSolutions's Avatar
    Join Date
    Feb 2008
    Location
    Bangalore
    Posts
    94

    Re: SQL query help: how to find 'last' record in a set of duplicates

    Quote 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

  11. #11
    New Member
    Join Date
    Mar 2010
    Posts
    2

    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..

  12. #12
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  13. #13
    New Member
    Join Date
    Mar 2010
    Posts
    2

    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
  •  



Click Here to Expand Forum to Full Width