By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,255 Members | 2,575 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,255 IT Pros & Developers. It's quick & easy.

how to write the select query for this situation

P: 35
Hi all
I have a table in which i have following values

id name grade city

1 aaa A hyd
2 bbb B che
3 ccc C del
4 ddd A hyd
5 eee C che
6 fff B che
7 ggg A hyd
8 hhh B che
9 iii A hyd
10 jjj C ban


Now i want to write a qurey which should give me the result as

all the name which are having grade and city combination as same

in this case o/p is

1 aaa A hyd
4 ddd A hyd
7 ggg A hyd
2 bbb B che
6 fff B che
8 hhh B che


how to write the query ... please someone help
Mar 13 '07 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 534
Let's consider this table
Expand|Select|Wrap|Line Numbers
  1. select * from game;
  2.  
  3.  id |  name  | grade |    city     
  4. ----+--------+-------+-------------
  5.   1 | john   | A     | boston
  6.   2 | mike   | A     | boston
  7.   3 | leo    | B     | LA
  8.   4 | vik    | C     | LA
  9.   5 | carrie | C     | NY
  10.   6 | john   | A     | cleveland
  11.   7 | mary   | B     | boston
  12.   8 | van    | B     | boston
  13.   9 | rose   | B     | springfield
  14.  10 | dan    | C     | NY
  15.  11 | mark   | C     | boston
  16.  12 | linda  | B     | NY
  17.  13 | max    | C     | NY
  18. (13 rows)
  19.  
In order to get what we need we will use the GROUP BY clause:
Expand|Select|Wrap|Line Numbers
  1. SELECT grade, city from game GROUP BY grade, city HAVING COUNT(*) > 1;
  2.  
  3.  grade |  city  
  4. -------+--------
  5.  A     | boston
  6.  B     | boston
  7.  C     | NY
  8. (3 rows)
  9.  
This gives us some idea on which combinations of grade and city should be included in the output.
However this is not the result we need, but rather something we can use
for a subquery, or perhaps for a join condition:

Expand|Select|Wrap|Line Numbers
  1. SELECT G1.* from game G1 JOIN 
  2.  (select grade, city from game group by grade, city having count(*) > 1) AS G2
  3. using (grade, city) order by grade, city;
  4.  
  5.   id |  name  | grade |  city  
  6. ----+--------+-------+--------
  7.   1 | john   | A     | boston
  8.   2 | mike   | A     | boston
  9.   7 | mary   | B     | boston
  10.   8 | van    | B     | boston
  11.  13 | max    | C     | NY
  12.  10 | dan    | C     | NY
  13.   5 | carrie | C     | NY
  14. (7 rows)
  15.  
  16.  
The result above includes rows with the same grade and city.

I would suggest these links for further reading:

SELECT (group by)
Table Expressions
Aggregate Functions
Mar 14 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.