Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    3

    Unanswered: Display Distinct Records question

    TableName: Order_Archive
    Fields:
    orderid
    load_date
    filename
    order_date
    dollar



    I load a file each week into a table, each file has unique orderid, load_date, filename, order_date and dollar. However the same orderid, order_date and dollar could appear in another file with different load_date and file_name.

    File1:
    orderid, load_date, file_name, order_date, dollar
    '1000', '2011-01-01', 'File1', '2011-01-01', '101'
    '1001', '2011-01-01', 'File1', '2011-01-01', '102'
    '1002', '2011-01-01', 'File1', '2011-01-01', '103'


    File2:
    orderid, load_date, file_name, order_date, dollar
    '1001', '2011-01-08', 'File2', '2011-01-01', '102'
    '1002', '2011-01-08', 'File2', '2011-01-01', '103'
    '1003', '2011-01-08', 'File2', '2011-01-01', '104'


    Question:
    Could anyone please advise whats is the best way to retrieve the distinct records
    that has the most recent load_date? expected results below:
    Expected Results:
    orderid, load_date, file_name, order_date, dollar
    '1000', '2011-01-01', 'File1', '2011-01-01', '101'
    '1001', '2011-01-08', 'File2', '2011-01-01', '102'
    '1002', '2011-01-08', 'File2', '2011-01-01', '103'
    '1003', '2011-01-08', 'File2', '2011-01-01', '104'

    Thank you in advance!

  2. #2
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    Not the prettiest but it works:

    Select OrderID, Load_Date, File_Name, Order_Date, Dollar
    From TABLE a
    Where Load_Date = (Select max(Load_Date) From TABLE Where orderid = a.orderid)
    Order by 1

  3. #3
    Join Date
    Nov 2011
    Posts
    21
    I have tried by this . check if this helps.


    Create Table dupTest (orderid int, load_date date,file_name varchar(100),Order_date date , doller int)

    Insert into dupTest
    Select '1000', '2011-01-01', 'File1', '2011-01-01', '101' union all
    Select '1001', '2011-01-01', 'File1', '2011-01-01', '102' union all
    Select '1002', '2011-01-01', 'File1', '2011-01-01', '103' union all
    Select '1001', '2011-01-08', 'File2', '2011-01-01', '102' union all
    Select '1002', '2011-01-08', 'File2', '2011-01-01', '103' union all
    Select '1003', '2011-01-08', 'File2', '2011-01-01', '104'


    Select * from dupTest except
    Select a.* from duptest a inner join duptest b on a.orderid=b.orderid and a.order_date=b.order_date where
    a.load_date < b.load_date

  4. #4
    Join Date
    Aug 2011
    Posts
    3
    Thank you for your help! This works. I have also found another solution.


    select ORDERID, LOAD_DATE, FILENAME, ORDER_DATE, DOLLAR
    from
    (
    select ORDERID, LOAD_DATE, FILENAME, ORDER_DATE, DOLLAR, ROW_NUMBER() over (partition by OrderID order by LOAD_DATE desc) as RowNum
    from TEST_TABLE
    )x
    where x.RowNum = 1

Posting Permissions

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