Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    5

    Unanswered: tweaking a select statement

    Hello, i have been searching the posts for awhile and am unable to find any results; so any help would be appreciated. I am using db2 8 client ans server ( though not related to any issue). The problem is i have a table similar to:

    Name | Date | Resturant | Meal_Cost | Reason
    --------------------------------------------
    joe 1/2/99 fish land 12.99 work
    joe 3/5/01 B King 5.99 work
    joe 3/6/01 Mc Dons 9.99 work
    jon 3/2/98 foods 22.99 work
    jon 3/5/99 foods 22.99 work
    jon 3/2/00 foods 22.99 work

    what i would like to do is select the most recent date from everyone that has eaten for work. Though the actual table is not this simple, they have all been joined with inner joins. The query i am running is similar to:

    Select Distinct(name), Max(Date), Resturant, Meal_Cost, Reason
    From table inner join table2 on .... inner join .....
    where (date < 12/30/01) AND (reason = 'work')
    group by Name

    Now when i run this i get some group by and order by exceptions; if i remove the 'group by name' and the max(date) to date, the query runs but i do not get the results i need.

    The only other solution i came up with was to have in the where clause a sub select to compare the Date with a MAX(date) of the Name...it works but is extremely SLOW!!...
    if anyone has a quick solution or a the proper way to group by in such a case please let me know.

    thanks

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: tweaking a select statement

    Just a thought:
    If the table is large and this query critical, you may consider indexing the table appropriately ...

    By critical, I mean, run quiet frequently and require a small response time ...

    Cheers
    Sathyaram

    Originally posted by mjoc13
    Hello, i have been searching the posts for awhile and am unable to find any results; so any help would be appreciated. I am using db2 8 client ans server ( though not related to any issue). The problem is i have a table similar to:

    Name | Date | Resturant | Meal_Cost | Reason
    --------------------------------------------
    joe 1/2/99 fish land 12.99 work
    joe 3/5/01 B King 5.99 work
    joe 3/6/01 Mc Dons 9.99 work
    jon 3/2/98 foods 22.99 work
    jon 3/5/99 foods 22.99 work
    jon 3/2/00 foods 22.99 work

    what i would like to do is select the most recent date from everyone that has eaten for work. Though the actual table is not this simple, they have all been joined with inner joins. The query i am running is similar to:

    Select Distinct(name), Max(Date), Resturant, Meal_Cost, Reason
    From table inner join table2 on .... inner join .....
    where (date < 12/30/01) AND (reason = 'work')
    group by Name

    Now when i run this i get some group by and order by exceptions; if i remove the 'group by name' and the max(date) to date, the query runs but i do not get the results i need.

    The only other solution i came up with was to have in the where clause a sub select to compare the Date with a MAX(date) of the Name...it works but is extremely SLOW!!...
    if anyone has a quick solution or a the proper way to group by in such a case please let me know.

    thanks
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Mar 2003
    Posts
    5
    Ok have may be on the right track for a solution, i changed

    Select Distinct(name), Max(Date), Resturant, Meal_Cost, Reason
    From table inner join table2 on .... inner join .....
    where (date < 12/30/01) AND (reason = 'work')
    group by Name


    TO

    Select Distinct(name), Date, Resturant, Meal_Cost, Reason
    From table inner join table2 on .... inner join .....
    where (date < 12/30/01) AND (reason = 'work')
    order date desc
    fetch first row only

    now i have a sorted result from most recent date, but when i put in 'fetch first row' it only gets the first row of the entire table. Is there a way to fetch only the first row of each person? Also is inner join the quickest way to join a table, or is their a better way to do so???

    any help would be appreciated.

    Thanks

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Without knowing exactly the query you are running...

    I have found that the optimiser will evaluate joins first and tehn look to the where predicate.

    If you rewrite your query so that any filters are applied in the INNER JOIN syntax and not as a post filter, you might see some benefit.

    e.g.

    select *
    from a inner join b
    on a.col1 = b.col1
    and a.col2 = 'xyz'
    inner join c on c.ol1 = b.col1
    and c.col2 = 'pqr'

    rather than...

    select *
    from a inner join b on a.col1 = b.col1
    inner join c on c.ol1 = b.col1
    where a.col2 = 'xyz'
    and c.col2 = 'pqr'

Posting Permissions

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