Results 1 to 3 of 3

Thread: Build the Query

  1. #1
    Join Date
    Jan 2004
    Posts
    66

    Unanswered: Build the Query

    Hi,

    I've a table and the data is as folows :

    ID PURDATE ORDER
    1 2/6/2004 2
    2 2/11/2004 1
    3 2/18/2004 3
    3 2/25/2004 2
    4 2/3/2004 3
    4 2/3/2004 3
    5 2/12/2004 5
    5 2/12/2004 6

    I need a single row for each ID , basis on some condition :

    1. One ID can have at most 2 rows

    2. For same ID , take the row , which has MAX PURDATE

    3.For same ID and same PURDATE , take the row , which has MAX ORDER

    4. If the two rows are same , then take on the basis of ROWID

    And the output will be like this :

    ID PURDATE ORDER
    1 2/6/2004 2
    2 2/11/2004 1
    3 2/25/2004 2
    4 2/3/2004 3
    5 2/12/2004 6


    Any help will be highly appreciated .................

    Thanks
    himridul

  2. #2
    Join Date
    Jan 2004
    Posts
    66
    Hi,

    I am using this query to get the result :

    SELECT ID,PURCHASEDATE,MAX(ORDERNO) FROM NIBHAJI N2 WHERE N2.PURCHASEDATE IN (SELECT MAX(PURCHASEDATE) FROM NIBHAJI N3 GROUP BY N3.ID)
    GROUP BY N2.ID,PURCHASEDATE ORDER BY N2.ID

    But for millions of data , I think it's not useful.

    Any idea , plz ............
    himridul

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I think that should be:

    SELECT ID,PURCHASEDATE,MAX(ORDERNO) FROM NIBHAJI N2 WHERE (N2.id,N2.PURCHASEDATE) IN (SELECT N2.id, MAX(PURCHASEDATE) FROM NIBHAJI N3 GROUP BY N3.ID)
    GROUP BY N2.ID,PURCHASEDATE ORDER BY N2.ID;

    Perhaps analytics might be more efficient. Something like:
    PHP Code:
    select idpurchasedatemax_orderno
    from
    select idpurchasedate,
             
    max(ordernoover (partition by idpurchasedate) as max_orderno,
             
    rank() over (partition by id order by purchasedate desc) as rnk
      from nibhaji
    )
    where rnk 1
    Something like that - I haven't tested it.

Posting Permissions

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