I must be missing something - SELECT MAX

  • Hello,

    I have a query that needs to be filtered by the most recent set_id.

    The table that holds the quartely set_ids has a Date_entered field.

    Where the set_id clause is in my query, I want the query to figure out on it's own, based on the entry date field in the same table, what the latest set_id is, and only return those rows that match it.

    I can't for the life of me figure out how to use the aggregate function MAX in my query - because it is aggregate it won't work unless I use a subquery... but all attempts fail.  or perhaps there is a simple way to do this?

    SELECT

     ll.pid AS PID,

     la.oic AS OIC, 

     lo.NAME_1 AS NAME1,

     lo.NAME_2 AS NAME2, 

     lo.ADDRESS_1 AS ADD1, 

     la.set_id AS SET_ID,

     (la.gen_impr_net + la.gen_land_net) AS Assesment

    FROM OWNERS lo INNER JOIN

     land_legal ll ON lo.EQUITY_ID = ll.legal_id INNER JOIN

     land_assess la ON lo.EQUITY_ID = la.legal_id INNER JOIN

     land_ass_set las ON la.set_id = las.set_id

    WHERE

     (lo.equity_type = 'OWNER') AND (lo.stop_date Is Null) AND (lo.EQUITY_SYSTEM = 'LAND')

     AND las.set_id = '887C20050503155740330000'

    How Do?

  • You have 2 tasks.

    1st - find latest set_id

    2nd - use this set_id as a filter.

    Subquery by definition.

    Sorry.

    _____________
    Code for TallyGenerator

  • Can also do with a derived table - step1 inside and step2 outside.

    select x.set_id, x.maxDAte, z.col1, z.col2...

    from  (

            select set_id, max(entrydate) as maxDate

            from myTable

            group by set_id

          ) x

    join    myTable z

    on      z.set_id = x.set_id

    and     z.entrydate = x.maxDate

    /Kenneth

  • SELECT

     ll.pid AS PID,

     la.oic AS OIC, 

     lo.NAME_1 AS NAME1,

     lo.NAME_2 AS NAME2, 

     lo.ADDRESS_1 AS ADD1, 

     la.set_id AS SET_ID,

     (la.gen_impr_net + la.gen_land_net) AS Assesment

    FROM OWNERS lo INNER JOIN

     land_legal ll ON lo.EQUITY_ID = ll.legal_id

    INNER JOIN

     land_assess la ON lo.EQUITY_ID = la.legal_id

    INNER JOIN

    (select top 1 set_id --, entry_date

    from land_ass_set

    order by entry_date desc

    ) las ON la.set_id = las.set_id

    WHERE

     (lo.equity_type = 'OWNER') AND (lo.stop_date Is Null) AND (lo.EQUITY_SYSTEM = 'LAND')

    -- AND las.set_id = '887C20050503155740330000'

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Brilliant!

    Thanks to all for the help.

    ALZDBA - Went wtih your suggestion, and all is well again with my query.

    Thanks again!

    heather

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply