Home » SQL & PL/SQL » SQL & PL/SQL » Ordering/Grouping Problem With 3 Joins
Ordering/Grouping Problem With 3 Joins [message #206326] Wed, 29 November 2006 10:31 Go to next message
Steve Zodiac
Messages: 3
Registered: November 2006
Junior Member
Hi,
I have a table/data structure similar to the following:
create table Client
(Client_id number, Address varchar2);

create table Jobs 
(Client_id number, Action_id number);

create table Action
(Action_id number, Priority number);

Insert into Client values (1, "1 Acacia Ave");
Insert into Client values (2, "7 High St");
Insert into Client values (3, "88 Pudding Lane");

Insert into Jobs values (1, 1);
Insert into Jobs values (1, 2);
Insert into Jobs values (1, 3);
Insert into Jobs values (2, 4);
Insert into Jobs values (2, 5);
Insert into Jobs values (2, 6);
Insert into Jobs values (3, 7);
Insert into Jobs values (3, 8);

Insert into Action values (1, 1);
Insert into Action values (2, 5);
Insert into Action values (3, 2);
Insert into Action values (4, 4);
Insert into Action values (5, 3);
Insert into Action values (6, 3);
Insert into Action values (7, 5);
Insert into Action values (8, 2);


Client is the "root" table. Jobs associates Action with Client and Action contains a range of columns one of which is Priority. I can get a list of Clients at Priority X or multiple rows per Client ordered by Priority, but what I'm failing to get returned is one row per client with the top Priority item for that Client ordered by Priority overall. Like this:
Address		Priority
-------------	------------
1 Acacia Ave	1
88 Pudding Lane	2
7 High St	3 

Any help would by much appreciated.
Re: Ordering/Grouping Problem With 3 Joins [message #206331 is a reply to message #206326] Wed, 29 November 2006 10:49 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
select address, priority
from 
(select c.address, a.priority, row_number() over (partition by c.client_id order by priority) as rn
from
client c, jobs j, action a
where c.client_id = j.client_id
and j.action_id = a.action_id)
where rn=1
order by priority
Re: Ordering/Grouping Problem With 3 Joins [message #206333 is a reply to message #206331] Wed, 29 November 2006 11:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why not keep it simple? Or did I misunderstand the requirements?
SQL> select clt.address
  2  ,      min(acn.priority)
  3  from   client clt
  4  ,      action acn
  5  ,      jobs   jos
  6  where  jos.client_id = clt.client_id
  7  and    acn.action_id = jos.action_id
  8  group  by clt.address;

ADDRESS                        MIN(ACN.PRIORITY)
------------------------------ -----------------
7 High St                                      3
88 Pudding Lane                                2
1 Acacia Ave                                   1
Re: Ordering/Grouping Problem With 3 Joins [message #206348 is a reply to message #206331] Wed, 29 November 2006 13:04 Go to previous messageGo to next message
Steve Zodiac
Messages: 3
Registered: November 2006
Junior Member
Cthulhu,
Thanks a bunch. I found this post: message #166521 before I started this thread but couldn't translate it to the "real" tables I'm querying. I had to add the table alias prefix to the inner order by in your example: "partition by c.client_id order by a.priority". Was that a typo? I'm on 10g release 1. Anyway, I "Get it" now and can build on it. Thanks again.
Re: Ordering/Grouping Problem With 3 Joins [message #206349 is a reply to message #206333] Wed, 29 November 2006 13:06 Go to previous message
Steve Zodiac
Messages: 3
Registered: November 2006
Junior Member
Thanks also, Frank. I'll extend both solutions and see what happens.
Previous Topic: Triggers and Date's
Next Topic: How to get a count for this error query
Goto Forum:
  


Current Time: Fri Dec 09 23:03:31 CST 2016

Total time taken to generate the page: 0.21667 seconds