| Query Help - Group By or Other solution? [message #293711] |
Mon, 14 January 2008 13:22  |
chrisK17
Messages: 12
Registered: January 2008
|
Junior Member |
|
|
Hey Folks,
I'm scratching my head over a query I need to write that I feel should be easier then I'm making it out to be. I have a table that contains information about a person. Each person will have multiple records, and each of these records are tracked by effective and end dates. Each person could have only one or as much as twenty records. The sequences will never overlap. I need a query that will return only that LATEST sequence for each person. There are non-date identification information I need on each record as well. Right now I'm doing something like this:
SELECT a.person_id, a.value1, a.value2,etc
FROM my_table a
where a.end_date = (select max(end_date) FROM my_table b WHERE b.person_id = a.person_id)
which is working, however, it is VERY inefficient and it is not an acceptable solution. I am hoping for some advice on how to re-write this query without having to use an aggregate sub-query. Maybe using a group by or some complex group by function? Any advice would be appreciated.
[Updated on: Mon, 14 January 2008 13:34] by Moderator Report message to a moderator |
|
|
|
| Re: Query Help - Group By or Other solution? [message #293712 is a reply to message #293711] |
Mon, 14 January 2008 13:38  |
 |
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| Quote: | how to re-write this query without having to use an aggregate sub-query.
|
Whatever you do you have to find the latest date, so the max.
You can use:
SELECT a.person_id, a.value1, a.value2,etc
FROM my_table a
where (a.person_id,a.end_date) IN
(select person_id, max(end_date) FROM my_table
And index (person_id,end_date).
Regards
Michel
|
|
|
|
|
|
| Re: Query Help - Group By or Other solution? [message #293899 is a reply to message #293711] |
Tue, 15 January 2008 09:52 |
chrisK17
Messages: 12
Registered: January 2008
|
Junior Member |
|
|
Interesting... thank you for the responses. However, in my example, I simplified things compared to how my tables are really structured. In what I'm working on now "my_table" is actually a join view. The first table it joins contains one record of information for each person, and there is an id column with an FK pointing to the the second table which contains the date sequences. So the person_id is actually in one table and the dates in another and I'm referencing it all as a single object through a view. The person ID is already indexed in the one table(it is the key of the table), but in the other table, would just adding an index on the end_date field accomplish a performance increase, even though i am accessing it all through a view?
Thanks so much for the advice.
|
|
|
|
|
|
|
|
|
|
| Re: Query Help - Group By or Other solution? [message #294055 is a reply to message #293711] |
Wed, 16 January 2008 05:01 |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
I have been hit with the more or less the same issue in my previous project where the table i am querying is partitioned and it is having more than billion rows in one partition. We tried with different versions of subqueries and finally we used the analytic functions and it improved the performance dramatically.
Query is something like this
select *
from (select <columns>,
rank(<column_interested)
over(partition by <identifier_for_the_group> order by <column_interested> rn
from <table_name>)
where rn = 1
The advantage by using analytic function is we are not scanning the table twice. Just a thought.
regards
Raj
[Updated on: Wed, 16 January 2008 05:21] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
In retrospect my original question was not well stated. However, I do feel that advice I have gotten here has helped though. Thanks everyone for your time.