Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2013
    Posts
    6

    Unanswered: SQL query - unique value from a dataset

    I am trying to create a query to return the latest record from a dataset.
    The code created so far returns multiple records, where I need a single record to be returned.

    Please see attached .pdf for full explanation

    Help would be appreciated, thank you.
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    with CTE_R as
    (
        SELECT 
            p.Member_ID, 
            r.Join_Date, 
            p.Surname, 
            p.Date_Of_Birth,
            r.Member_Signed_Date,
            ROW_NUMBER() OVER(PARTITION BY p.Member_ID ORDER BY r.Join_Date DESC) as RowNum
        FROM tblMembership_Plan AS r 
        LEFT OUTER JOIN tblMember_Details AS p 
            ON p.Member_ID = r.Member_ID
    )
    
    
    select * from CTE_R
    where RowNum = 1
    Hope this helps.

  3. #3
    Join Date
    Feb 2013
    Posts
    6
    Thank you again, very helpful, this looks great and works perfectly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •