Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2009
    Posts
    25

    Unanswered: Sql using max or top

    I have a table with the values below

    Code:
    CUST_ID    ORDER_TYPE           ORDER_NO    EFF_BEGIN_DT
    ====================================================
    1234         CUST                    070805       01/01/2008  
    1234         CUST                    370806       12/01/2008

    I need to retrieve the ORDER_NO for a particular CUST_ID which has the greatest EFF_BEGIN_DT, either using a max or the top 1 row using the order by eff_begin_dt . I know I can do this querying the same table twice but want to avoid doing that

    Thanks in advance

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Something like this?
    Code:
    select t.cust_id, t.order_no
    from your_table t
    where t.eff_begin_dt = (select max(t1.eff_begin_dt)
                            from your_table t1
                            where t1.cust_id = t.cust_id
                           );

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    select cust_id,order_no
    from
    (
    select t.cust_id, t.order_no
    from your_table t
    order by t.eff_begin_dt
    )
    where rownum <= 1;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Bill, would that be OK? Did you mean "ORDER BY t.eff_begin_dt DESC", perhaps? Also, what if there were different CUST_ID's? As I don't have his schema, I'll go with Scott's one; for a certain analogy, DEPTNO = CUST_ID, ENAME = ORDER_NO, HIREDATE = EFF_BEGIN_DT.

    First, my way:
    Code:
    SQL> select deptno, ename, hiredate
      2  from emp e
      3  where e.hiredate = (select max(e1.hiredate)
      4                      from emp e1
      5                      where e1.deptno = e.deptno
      6                     )
      7  order by deptno;
    
        DEPTNO ENAME      HIREDATE
    ---------- ---------- ----------
            10 MILLER     23.01.1982
            20 ADAMS      12.01.1983
            30 JAMES      03.12.1981
    Now, your way:
    Code:
    SQL> select deptno, ename, hiredate
      2  from (select deptno, ename, hiredate
      3        from emp
      4        order by hiredate desc
      5       )
      6  where rownum <= 1;
    
        DEPTNO ENAME      HIREDATE
    ---------- ---------- ----------
            20 ADAMS      12.01.1983
    
    SQL>
    What do you think?

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking All the roads lead to...?


    Maybe this way?:
    Code:
    WITH
      orders AS
      (SELECT 1234 CUST_ID,'CUST' ORDER_TYPE,'070805' ORDER_NO, TO_DATE('01/01/2008','MM/DD/YYYY') EFF_BEGIN_DT FROM DUAL UNION
       SELECT 1234        ,'CUST'           ,'370806'         , TO_DATE('12/01/2008','MM/DD/YYYY')              FROM DUAL UNION
       SELECT 2345        ,'CUST'           ,'708061'         , TO_DATE('11/11/2008','MM/DD/YYYY')              FROM DUAL UNION
       SELECT 2345        ,'CUST'           ,'708063'         , TO_DATE('02/02/2009','MM/DD/YYYY')              FROM DUAL
      )
     SELECT  cust_id, order_no, eff_begin_dt
       FROM  (
       SELECT t.cust_id, t.order_no, eff_begin_dt
            , DENSE_RANK() OVER (PARTITION BY cust_id ORDER BY eff_begin_dt DESC) rn
         FROM orders t
      )
      WHERE rn = 1
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    That was my initial reaction to Bill's solution but then I reread the OP's question and it sounds like he's looking for a single customer so that would be fine in that situation.

    It would be nice if analytic functions had a having clause or you could use them in the where clause.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Your right it should have been DESC and the optimizer is smart enough to just find the first row and return it.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Feb 2009
    Posts
    25
    Thanks for all your input however it does not work in all situations for example if my table is as below:

    CUST_ID ORDER_TYPE ORDER_NO EFF_BEGIN_DT UPDATED_DATE
    ================================================== ================
    1234 CUST 070805 01/01/2008 01/01/2009
    1234 CUST 370806 01/01/2008 01/02/2009
    4567 CUST 334489 01/01/2008 01/01/2009
    4567 CUST 446789 12/31/2008 01/03/2009

    Firstly I want to run the query for each CUST_ID in the table if I do rn < 2 etc it will return me the value only for the first customer ...also sometimes the eff_begin_dt is the same for 2 records so I need to combine it with the updated date (is a timestamp field) in order to get a unique value .. any suggestions?

  9. #9
    Join Date
    Feb 2009
    Posts
    25
    I guess I can add the UPDATED_DATE to the order by clause in the partition and that would work... will need to test it some more ...thanks

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Obviously, if two (or more) records contain the same value in a column, there's no way to determine which one of them is "the first" (if checking only that very value).

  11. #11
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by artacus72 View Post
    TIt would be nice if analytic functions had a having clause or you could use them in the where clause.
    Well you can always put your query with the analytical functions into a common table expression (WITH ... ) or a derived table
    Code:
    SELECT * 
    FROM (
       ... here goes the real query ...
    )
    WHERE col_from_anlytical_function = 42;

Posting Permissions

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