Home » SQL & PL/SQL » SQL & PL/SQL » Query Help - Group By or Other solution?
Query Help - Group By or Other solution? [message #293711] Mon, 14 January 2008 13:22 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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 #293761 is a reply to message #293712] Mon, 14 January 2008 19:57 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This type of query can actually be made VERY efficient, but you MUST:
- Index (person_id, end_date)
- Gather statistics

See here (bottom of page) for an explanation.

Ross Leishman
Re: Query Help - Group By or Other solution? [message #293899 is a reply to message #293711] Tue, 15 January 2008 09:52 Go to previous messageGo to next message
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 #293989 is a reply to message #293899] Tue, 15 January 2008 20:48 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
So what you're saying is that you didn't give us all the information and the advice was useless. Wow, I wouldn't have anticipated that.
Re: Query Help - Group By or Other solution? [message #293998 is a reply to message #293989] Tue, 15 January 2008 23:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the advice was useless

I should not say that, I should say the avice is a good one for the question that was posted.
The fact that the question has nothing to do with OP's real problem is irrelevant concerning the quality of the answer.
OP has the answer to his question. Razz

Regards
Michel
Re: Query Help - Group By or Other solution? [message #293999 is a reply to message #293998] Tue, 15 January 2008 23:14 Go to previous messageGo to next message
chrisK17
Messages: 12
Registered: January 2008
Junior Member
Michel - thank you for stating it so well. Sometimes constructive criticism is better then useless sarcasm Confused 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.
Re: Query Help - Group By or Other solution? [message #294055 is a reply to message #293711] Wed, 16 January 2008 05:01 Go to previous messageGo to next message
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

Re: Query Help - Group By or Other solution? [message #294064 is a reply to message #294055] Wed, 16 January 2008 05:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The disadvantage is you have to full scan the table.

Regards
Michel
Re: Query Help - Group By or Other solution? [message #294069 is a reply to message #294064] Wed, 16 January 2008 05:46 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
The disadvantage is you have to full scan the table.
How about this
select person_id, max(end_date) FROM my_table 

Regards

Raj
Re: Query Help - Group By or Other solution? [message #294083 is a reply to message #294069] Wed, 16 January 2008 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The fastest solution depends on indexes and data.
In this case, most often, analytic functions are only faster when there are few rows or few rows (here different end_date) per category (here person_id).
Aggregate functions are faster and faster when the ratio number of rows per category increases due to the data to handle.
MAX only keeps one row for each category (the max one). RANK must keeps all rows.

Regards
Michel
Re: Query Help - Group By or Other solution? [message #294090 is a reply to message #294083] Wed, 16 January 2008 06:52 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I will come back with my tkprof observation.

Regards

Raj
Previous Topic: How to query across schemas
Next Topic: Date format
Goto Forum:
  


Current Time: Sat Dec 10 14:49:01 CST 2016

Total time taken to generate the page: 0.07716 seconds