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