Change theme: 
Search for :    
Previous Blogs
Transformers Blogs
Misc Blogs
Computer/Tech Logs
Movie Reviews
Popular Blogs
ASCII chart
Comment from Tamera on December 24, 2015
Configuring Speedstream 6520 For Modem Only Mode
Comment from Janet on September 13, 2015
Transformers Reviews: MP-19 Smokescreen
Comment from Silagra on August 1, 2015
Transformers Reviews: MP-21 Masterpiece (Bumble) Bee
Comment from google authorship check on June 26, 2015
Aww...It's a Cat...Meow
Comment from vxt on June 4, 2015


RSS Facebook Twitter Instagram
My Tweets

Oracle: Select Distinct From a Many-To-Many Query
Once upon a time, I wrote this blog that showed how you could select distinct based on a single column, while at the same time select other fields from the table.

Looking back on that blog, I didn't really explain why you would need something like this and that example wasn't really a good one since in most situations, the reason you would need this is on a one-to-many or many-to-many relationship.

Going back to that example, I would change company to resident and this would have been a more useful query:

select r.id, r.fname, r.lname from resident r inner join address a on r.id=a.resident_id order by a.move_in_date;

In this example, we're storing a history of a resident's address and the above query would get a history of their address ordered by the move-in date. But suppose you only wanted the current address.

In MySQL, you could actually do this:

select r.id, r.fname, r.lname, max(a.move_in_date) from resident r inner join address a on r.id=a.resident_id group by r.id order by a.move_in_date;

This is not something you can do in Oracle. In Oracle, you would need to do a sub-query:

select r.id, r.fname, r.lname, a.move_in_date
from resident r, address a
where r.id=a.resident_id
and a.move_in_date=(select max(a2.move_in_date) from address a2, resident r2 where r2.id=a2.resident_id and r2.id=r.id);

What this query is doing is it's getting all the addresses for resident r. Then, it also requires that the address move_in_date is equal to the max move_in_date of the resident (r2.id=r.id).
Comments

Post a Comment

Total records: 1
Posted by fotografo matrimonio reportage on 2014-10-14 03:09:25Post #1 - Post link
Appreciating the persistence you put into your ste and detailed information you provide. It's good to come across a blog every once in a while that isn't the same unwanted rehashed material. Wonderful read! I've bookmarked your site and I'm including your RSS feeds to my Google account.

Copyright © Viet Tran. All Rights Reserved. Powered by Content ManagerTM | Top