Home » SQL & PL/SQL » SQL & PL/SQL » Query Help!
Query Help! [message #294102] |
Wed, 16 January 2008 08:11  |
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   |
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 #294116 is a reply to message #294102] |
Wed, 16 January 2008 09:06   |
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   |
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   |
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   |
Frank
Messages: 7901 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  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|
Goto Forum:
Current Time: Tue Feb 11 05:03:13 CST 2025
|