Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2014
    Posts
    1

    Unanswered: Inner Join with max value on multiple columns

    I tried to search the threads and google but couldn’t find any workable solution. If this is already discussed in another thread please provide me the link to the same –
    Here is my problem –

    I have a table EMPLOYEE

    Emp_ID Salary update_dt update_time
    1 10 01/01/2014 13:10
    1 12 01/01/2014 14:15
    1 18 01/01/2012 01:20
    2 25 02/02/2013 13:25
    2 20 02/02/2012 14:25
    2 30 02/02/2012 13:25

    I need the latest salary for each employee –
    So my output should look like –

    Emp_ID Salary
    1 12
    2 25

    Right now I am using subquery to do this –

    SELECT Emp_ID, Salary
    FROM EMPLOYEE E1
    WHERE UPDATE_DT =
    (SELECT MAX(UPDATE_DT)
    FROM EMPLOYEE E2
    WHERE E1.EMP_ID = E2.EMP_ID
    AND E2.UPDATE_TIME =
    (SELECT MAX(UPDATE_TIME)
    FROM EMPLOYEE E3
    WHERE E1.EMP_ID = E3.EMP_ID))

    This query seems to be running fine but it is taking lot of time.

    Is it possible to write this query differently may be inner joining three tables or something like that?

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    depending on your data, maybe:
    Code:
    SELECT Emp_ID, Salary
    FROM EMPLOYEE E1
    where emp_id =?
    order by UPDATE_DT desc, UPDATE_TIME desc
    fetch first 1 row only

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by jamesyahoo View Post

    Right now I am using subquery to do this –

    SELECT Emp_ID, Salary
    FROM EMPLOYEE E1
    WHERE UPDATE_DT =
    (SELECT MAX(UPDATE_DT)
    FROM EMPLOYEE E2
    WHERE E1.EMP_ID = E2.EMP_ID
    AND E2.UPDATE_TIME =
    (SELECT MAX(UPDATE_TIME)
    FROM EMPLOYEE E3
    WHERE E1.EMP_ID = E3.EMP_ID))

    This query seems to be running fine but it is taking lot of time.

    Did you tried your query?

    It will not return your expected result, like...
    Code:
    WITH
     employee
    ( emp_id , salary , update_dt , update_time ) AS (
    VALUES
      ( 1 , 10 , DATE('01/01/2014') , TIME('13:10') )
    , ( 1 , 12 , '01/01/2014' , '14:15' )
    , ( 1 , 18 , '01/01/2012' , '01:20' )
    , ( 2 , 25 , '02/02/2013' , '13:25' )
    , ( 2 , 20 , '02/02/2012' , '14:25' )
    , ( 2 , 30 , '02/02/2012' , '13:25' )
    )
    SELECT Emp_ID, Salary
    FROM EMPLOYEE E1
    WHERE UPDATE_DT = 
    (SELECT MAX(UPDATE_DT)
    FROM EMPLOYEE E2
    WHERE E1.EMP_ID = E2.EMP_ID
    AND E2.UPDATE_TIME =
    (SELECT MAX(UPDATE_TIME)
    FROM EMPLOYEE E3
    WHERE E1.EMP_ID = E3.EMP_ID))
    ;
    ------------------------------------------------------------------------------
    
    EMP_ID      SALARY     
    ----------- -----------
              1          10
              1          12
              2          20
              2          30
    
      4 record(s) selected.
    Before considering efficient query, you should try to make working query.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The design of your table was the cause of the difficulty to make the query.
    Combine update_dt and update_time columns into one column, say , update_timestamp.

    A temporary alternative might be to use TIMESTAMP built-in function, like ...
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     employee
    ( emp_id , salary , update_dt , update_time ) AS (
    VALUES
      ( 1 , 10 , DATE('01/01/2014') , TIME('13:10') )
    , ( 1 , 12 , '01/01/2014' , '14:15' )
    , ( 1 , 18 , '01/01/2012' , '01:20' )
    , ( 2 , 25 , '02/02/2013' , '13:25' )
    , ( 2 , 20 , '02/02/2012' , '14:25' )
    , ( 2 , 30 , '02/02/2012' , '13:25' )
    )
    SELECT Emp_ID, Salary
     FROM  employee e1
     WHERE TIMESTAMP(update_dt , update_time)
           = (SELECT MAX( TIMESTAMP(update_dt , update_time) )
               FROM  employee e2
               WHERE e2.emp_id = e1.emp_id
             )
    ;
    ------------------------------------------------------------------------------
    
    EMP_ID      SALARY     
    ----------- -----------
              1          12
              2          25
    
      2 record(s) selected.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by dav1mo View Post
    depending on your data, maybe:
    Code:
    SELECT Emp_ID, Salary
    FROM EMPLOYEE E1
    where emp_id =?
    order by UPDATE_DT desc, UPDATE_TIME desc
    fetch first 1 row only
    The requirement was to get the latest salary for each employee, like

    Emp_ID Salary
    1 12
    2 25

    So, "fetch first 1 row only" might be not appropriate.

    An alternative may be to use
    ROW_NUMBER() OVER(PARTITION BY emp_id order by UPDATE_DT desc, UPDATE_TIME desc)
    in subquery.

  6. #6
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by jamesyahoo View Post
    I tried to search the threads and google but couldn’t find any workable solution. If this is already discussed in another thread please provide me the link to the same –
    Here is my problem –

    I have a table EMPLOYEE

    Emp_ID Salary update_dt update_time
    1 10 01/01/2014 13:10
    1 12 01/01/2014 14:15
    1 18 01/01/2012 01:20
    2 25 02/02/2013 13:25
    2 20 02/02/2012 14:25
    2 30 02/02/2012 13:25

    I need the latest salary for each employee –
    So my output should look like –

    Emp_ID Salary
    1 12
    2 25

    Right now I am using subquery to do this –

    SELECT Emp_ID, Salary
    FROM EMPLOYEE E1
    WHERE UPDATE_DT =
    (SELECT MAX(UPDATE_DT)
    FROM EMPLOYEE E2
    WHERE E1.EMP_ID = E2.EMP_ID
    AND E2.UPDATE_TIME =
    (SELECT MAX(UPDATE_TIME)
    FROM EMPLOYEE E3
    WHERE E1.EMP_ID = E3.EMP_ID))

    This query seems to be running fine but it is taking lot of time.

    Is it possible to write this query differently may be inner joining three tables or something like that?
    You can use the row_number function, partition by Emp_ID and order by date desc, time desc and then select all rows with row_number 1
    --
    Lennart

Posting Permissions

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