Home » SQL & PL/SQL » SQL & PL/SQL » Group By
Group By [message #430879] Thu, 12 November 2009 17:27 Go to next message
theladyd
Messages: 13
Registered: September 2008
Junior Member
I've read numerous post and tips about the group by and I understand; however, if I follow the group by rule of including all the Select fields in the Group By, then the results are meaningless. (Sample table data listed below)

ORG ROLE EMPNO Name Phone
1013330604 1 12345 Curly 5852723219
1013330604 2 678910 Larry 5852723251
1013330604 4 111213 Moe 6036270286
1013330605 4 141516 Abbot 6036270286
1013330605 2 171819 Costello 5852723251

I only want to list the rows that have the minimum role, so my results should be the example listed below. I only want to group by org.

ORG ROLE EMPNO Name Phone
1013330604 1 12345 Curly 5852723219
1013330605 2 171819 Costello 5852723251


SELECT
ORG, MIN(ROLE), EMPNO, PNAME, PHONE
FROM TABLE
GROUP BY ORG, EMPNO, PNAME, PHONE;

Is there a simple way to write the query to achieve desired results?
Re: Group By [message #430881 is a reply to message #430879] Thu, 12 November 2009 18:07 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
SELECT ORG, ROLE1, EMPNO, PNAME, PHONE
FROM TABLE1
WHERE (ORG, ROLE1) IN (SELECT ORG, MIN(ROLE1) FROM TABLE1 GROUP BY ORG);

You should not be using KEYWORDS as DB object names.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/


[Updated on: Thu, 12 November 2009 19:22]

Report message to a moderator

Re: Group By [message #430925 is a reply to message #430879] Fri, 13 November 2009 03:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
SELECT ORG, ROLE, EMPNO, PNAME, PHONE
FROM (SELECT ORG, ROLE, EMPNO, PNAME, PHONE
            ,MIN(ROLE) OVER (PARTITION BY ORG ORDER BY ROLE) min_role
      FROM   TABLE)
WHERE role = min_role


Re: Group By [message #430980 is a reply to message #430925] Fri, 13 November 2009 08:56 Go to previous message
theladyd
Messages: 13
Registered: September 2008
Junior Member
Thank you both for you quick response. Like many others, I am self taught and really rely on the many sites and forums for guidance. So I do search and try many scenarios before asking for help. (I don't use keywords in my sql, but I never use my true field names when posting an sql).
Previous Topic: Merging records in an output file
Next Topic: Converting a sql script to pl/sql
Goto Forum:
  


Current Time: Wed Sep 28 12:36:25 CDT 2016

Total time taken to generate the page: 0.06646 seconds