DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Query

  1. #1
    Join Date
    Jan 2000
    Posts
    6

    Post

    Hi All,

    I want to write a query for Rate table. It has four columns empl_uno,rate,eff_date,last_modify. It has multiple rates for single employee.

    emlp_uno rate eff_date last_modify
    ----------------------------------------------------------------
    207 375.00 1/1/1990 11/23/1999 4:10:47 PM
    207 395.00 4/22/2000 5/18/2000 3:13:01 PM
    347 235.00 1/1/1990 11/8/1999 8:20:46 PM
    347 235.00 4/22/2000 5/18/2000 3:27:21 PM
    347 250.00 8/1/2000 9/25/2000 1:12:44 PM
    375 130.00 1/1/1999 9/25/2000 1:24:44 PM
    375 150.00 8/1/2000 9/25/2000 1:29:44 PM

    I want to return latest rates for employee for example

    emlp_uno rate eff_date last_modify
    ----------------------------------------------------------
    207 395.00 4/22/2000 5/18/2000 3:13:01 PM
    347 250.00 8/1/2000 9/25/2000 1:12:44 PM
    375 150.00 8/1/2000 9/25/2000 1:29:44 PM

    I wrote query like
    select empl_uno,rate,max(eff_date),last_modify from Rate
    group by empl_uno,rate,eff_date,last_modify

    and I get result as

    emlp_uno rate eff_date last_modify
    ----------------------------------------------------------------
    207 375.00 1/1/1990 11/23/1999 4:10:47 PM
    207 395.00 4/22/2000 5/18/2000 3:13:01 PM
    347 235.00 1/1/1990 11/8/1999 8:20:46 PM
    347 235.00 4/22/2000 5/18/2000 3:27:21 PM
    347 250.00 8/1/2000 9/25/2000 1:12:44 PM
    375 130.00 1/1/1999 9/25/2000 1:24:44 PM
    375 150.00 8/1/2000 9/25/2000 1:29:44 PM

    which is not right. Please help me with this. Thanks in advance

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    select a.empl_uno,
    a.rate,
    a.eff_date,
    a.last_modify
    from rate a
    where a.eff_date = ( select max(b.eff_date)
    from rate b
    where a.empl_uno = b.empl_uno);

  3. #3
    Join Date
    Nov 1999
    Location
    Elbert, Colorado, USA
    Posts
    81
    I believe the following will work if you are using Oracle:

    select a.empl_uno,
    a.rate,
    a.eff_date,
    a.last_modify
    from rate a, (SELECT empl_uno, max(eff_date) max_date
    FROM rate
    GROUP BY empl_uno) b
    where a.eff_date = b.max_date;

    This should be more efficient since it eliminates the correlated subquery.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    The cost will be higher if we use Group By where as the correlated query will give less cost.

    I have analyzed both my query and Carp's query. I found that my query uses Sort Aggregate and Carp's query uses Sort Group by.

    I feel SORT AGGREGATE will be faster than SORT GROUP BY.

    Carp, do you have experience on this?



Posting Permissions

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


Click Here to Expand Forum to Full Width