Home » SQL & PL/SQL » SQL & PL/SQL » Query Help!
Query Help! [message #294102] Wed, 16 January 2008 08:11 Go to next message
bryangcampbell
Messages: 2
Registered: January 2008
Location: Boston, MA, USA
Junior Member
I'm a software engineer and don't usually write SQL. When I do it is usually an easy query. I'm trying to get information from a table but I want to do it using SQL to get just what I want rather then bringing in everything into my software and sorting through it to pick out what I need.
Here is the issue: Say I have the following table:
STATE  COUNTY   CITY    POP
MA     Suffolk  Boston  15
MA     Suffolk  Revere  9
MA     Suffolk  Nahant  1
MA     Norfolk  Quincy  12
MA     Norfolk  Milton  13
MA     Norfolk  Canton  8
MA     Essex    Lynn    10
MA     Essex    Peabody 7
MA     Essex    Saugus  11


I want to find the city in each county that has the highest population. In other words the query should return
MA     Suffolk  Boston  15
MA     Norfolk  Milton  13
MA     Essex    Saugus  11


I know I could easily get at what I want by bringing the whole table into my application and doing some processing there but it seems like the property designed SQL statement would do the work for me. I've only written pretty simple SQL and have not been able to come up with the right query yet. Any help would be appreciated. Thanks!

Bryan
Re: Query Help! [message #294105 is a reply to message #294102] Wed, 16 January 2008 08:23 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
First of all, I would like to thank you personally for formatting your post (especially being your first post you have formatted it).

There are many ways to do it. I will give you one of the version of it.

I made an assumption that you want to group it by state and county.

SQL> with t as
  2  (select 'MA' state, 'Suffolk' county, 'boston' city, 15 pop from dual
  3   union all
  4   select 'MA' state, 'Suffolk' county, 'boston' city, 9 pop from dual
  5   union all
  6   select 'MA' state, 'Suffolk' county, 'boston' city, 1 pop from dual
  7   union all
  8   select 'MA' state, 'Norfolk' county, 'boston' city, 12 pop from dual
  9   union all
 10   select 'MA' state, 'Norfolk' county, 'boston' city, 13 pop from dual
 11   union all
 12   select 'MA' state, 'Essex' county, 'Lynn' city, 10 pop from dual
 13   union all
 14   select 'MA' state, 'Essex' county, 'Peabody' city, 7 pop from dual
 15   union all
 16   select 'MA' state, 'Essex' county, 'Saugus' city, 11 pop from dual
 17  )
select * from t;
 18
ST COUNTY  CITY           POP
-- ------- ------- ----------
MA Suffolk boston          15
MA Suffolk boston           9
MA Suffolk boston           1
MA Norfolk boston          12
MA Norfolk boston          13
MA Essex   Lynn            10
MA Essex   Peabody          7
MA Essex   Saugus          11



  1  with t as
  2  (select 'MA' state, 'Suffolk' county, 'boston' city, 15 pop from dual
  3   union all
  4   select 'MA' state, 'Suffolk' county, 'boston' city, 9 pop from dual
  5   union all
  6   select 'MA' state, 'Suffolk' county, 'boston' city, 1 pop from dual
  7   union all
  8   select 'MA' state, 'Norfolk' county, 'boston' city, 12 pop from dual
  9   union all
 10   select 'MA' state, 'Norfolk' county, 'boston' city, 13 pop from dual
 11   union all
 12   select 'MA' state, 'Essex' county, 'Lynn' city, 10 pop from dual
 13   union all
 14   select 'MA' state, 'Essex' county, 'Peabody' city, 7 pop from dual
 15   union all
 16   select 'MA' state, 'Essex' county, 'Saugus' city, 11 pop from dual
 17  )
 18* select t.*, rank() over(partition by state, county order by pop desc) rn from t
SQL> /

ST COUNTY  CITY           POP         RN
-- ------- ------- ---------- ----------
MA Essex   Saugus          11          1
MA Essex   Lynn            10          2
MA Essex   Peabody          7          3
MA Norfolk boston          13          1
MA Norfolk boston          12          2
MA Suffolk boston          15          1
MA Suffolk boston           9          2
MA Suffolk boston           1          3


  1  with t as
  2  (select 'MA' state, 'Suffolk' county, 'boston' city, 15 pop from dual
  3   union all
  4   select 'MA' state, 'Suffolk' county, 'boston' city, 9 pop from dual
  5   union all
  6   select 'MA' state, 'Suffolk' county, 'boston' city, 1 pop from dual
  7   union all
  8   select 'MA' state, 'Norfolk' county, 'boston' city, 12 pop from dual
  9   union all
 10   select 'MA' state, 'Norfolk' county, 'boston' city, 13 pop from dual
 11   union all
 12   select 'MA' state, 'Essex' county, 'Lynn' city, 10 pop from dual
 13   union all
 14   select 'MA' state, 'Essex' county, 'Peabody' city, 7 pop from dual
 15   union all
 16   select 'MA' state, 'Essex' county, 'Saugus' city, 11 pop from dual
 17  )
 18  select state, county, city, pop from
 19  (
 20  select t.*, rank() over(partition by state, county order by pop desc) rn from t
 21* ) where rn = 1
SQL> /

ST COUNTY  CITY           POP
-- ------- ------- ----------
MA Essex   Saugus          11
MA Norfolk boston          13
MA Suffolk boston          15


I know that I am spoonfeeding since you have formatted your post nicely I am giving it to you.

Happy learning sql.

From next time if you could give us the insert script along with your question it will be even more helpful for anybody to help you out.

Regards

Raj.


Re: Query Help! [message #294106 is a reply to message #294102] Wed, 16 January 2008 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And now the question for 1$: if two have the highest population which one(s) must be returned?

RANK
DENSE_RANK
ROW_NUMBER

Regards
Michel
Re: Query Help! [message #294116 is a reply to message #294102] Wed, 16 January 2008 09:06 Go to previous messageGo to next message
bryangcampbell
Messages: 2
Registered: January 2008
Location: Boston, MA, USA
Junior Member
Thank you Raj for taking it easy on me. It was very helpful and what's more the solution worked great!
Thanks Michel for the reading material, very helpful/informative. In answer to your question, for my specific application that scenario could never come up, but if it did, I wouldn't care which it returned, I would care however if it returned both!

Thanks again both of you...

Bryan
Re: Query Help! [message #294125 is a reply to message #294102] Wed, 16 January 2008 09:50 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member

Another approach is 

select * from country_details 
where pop in (select max(pop) pop from country_details
              group by state,county )
order by pop desc
o/p:
STATE      COUNTY                         CITY                   POP
---------- ------------------------------ --------------- ----------
MA         Suffolk                        Boston                  15
MA         Norfolk                        Milton                  13
MA         Essex                          Saugus                  11
Re: Query Help! [message #294126 is a reply to message #294116] Wed, 16 January 2008 09:50 Go to previous messageGo to next message
ashwin_tampa
Messages: 40
Registered: October 2005
Member
May be this help in that situation ?




CREATE TABLE COUNTY_POP
(STATE CHAR(2),
 COUNTY VARCHAR2(20),
 CITY   VARCHAR2(20),
 POP    NUMBER);

INSERT ALL 
INTO COUNTY_POP VALUES ('MA', 'Suffolk',  'Boston',  15)
INTO COUNTY_POP VALUES ('MA', 'Suffolk',  'Revere',  9)
INTO COUNTY_POP VALUES ('MA', 'Suffolk',  'Nahant',  1)
INTO COUNTY_POP VALUES ('MA', 'Norfolk',  'Quincy',  12)
INTO COUNTY_POP VALUES ('MA', 'Norfolk',  'Milton',  13)
INTO COUNTY_POP VALUES ('MA', 'Norfolk',  'Canton',  8)
INTO COUNTY_POP VALUES ('MA', 'Essex',    'Lynn',  10)
INTO COUNTY_POP VALUES ('MA', 'Essex',   'Peabody', 7)
INTO COUNTY_POP VALUES ('MA', 'Essex',    'Saugus',  11)
INTO COUNTY_POP VALUES ('MA', 'Essex',    'Saugus',  11)
INTO COUNTY_POP VALUES ('MA', 'Essex',    'Saugus',  15)
INTO COUNTY_POP VALUES ('MA', 'Suffolk',  'Boston',  13)
INTO COUNTY_POP VALUES ('MA', 'Essex',    'Saugus',  15)
SELECT * FROM DUAL;

COMMIT;

SQL> SELECT  state,county,city,POP,RANK
  2   FROM (SELECT  state,county,city,POP,RANK
  3    FROM (SELECT state,county,city,POP,
  4     ROW_NUMBER() OVER (PARTITION BY COUNTY ORDER BY POP DESC) "RANK"
  5    FROM county_pop)
  6  --   WHERE RANK = 1
  7  )
  8  /

ST COUNTY               CITY                        POP       RANK
-- -------------------- -------------------- ---------- ----------
MA Essex                Saugus                       15          1  <--
MA Essex                Saugus                       15          2  <--
MA Essex                Saugus                       11          3
MA Essex                Saugus                       11          4
MA Essex                Lynn                         10          5
MA Essex                Peabody                       7          6
MA Norfolk              Milton                       13          1  <--
MA Norfolk              Quincy                       12          2
MA Norfolk              Canton                        8          3
MA Suffolk              Boston                       15          1
MA Suffolk              Boston                       13          2  <--
MA Suffolk              Revere                        9          3
MA Suffolk              Nahant                        1          4

13 rows selected.

SQL> SELECT  state,county,city,POP
  2   FROM (SELECT  state,county,city,POP,RANK
  3    FROM (SELECT state,county,city,POP,
  4     ROW_NUMBER() OVER (PARTITION BY COUNTY ORDER BY POP DESC) "RANK"
  5    FROM county_pop)
  6     WHERE RANK = 1)
  7  /

ST COUNTY               CITY                        POP 
-- -------------------- -------------------- ---------- 
MA Essex                Saugus                       15 
MA Norfolk              Milton                       13 
MA Suffolk              Boston                       15 

SQL>




Re: Query Help! [message #294129 is a reply to message #294102] Wed, 16 January 2008 09:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
bryangcampbell wrote on Wed, 16 January 2008 15:11
[...] but I want to do it using SQL to get just what I want rather then bringing in everything into my software and sorting through it to pick out what I need

Oh man, I love you...

[Edit: Explanation: the number of people I have to convince that this is EXACTLY the way to go!]

[Updated on: Wed, 16 January 2008 09:53]

Report message to a moderator

Re: Query Help! [message #294132 is a reply to message #294116] Wed, 16 January 2008 10:13 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but if it did, I wouldn't care which it returned, I would care however if it returned both!

In this case row_number is the function you have to use not rank or dense_rank.

Regards
Michel
Previous Topic: last day of quarter
Next Topic: Replace string to array - pl/sql
Goto Forum:
  


Current Time: Tue Dec 06 08:46:34 CST 2016

Total time taken to generate the page: 0.14541 seconds