how to write the select query for this situation

[123456prakash]

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

[michaelb]

Let's consider this table

select * from game;
 
 id |  name  | grade |    city     
----+--------+-------+-------------
  1 | john   | A     | boston
  2 | mike   | A     | boston
  3 | leo    | B     | LA
  4 | vik    | C     | LA
  5 | carrie | C     | NY
  6 | john   | A     | cleveland
  7 | mary   | B     | boston
  8 | van    | B     | boston
  9 | rose   | B     | springfield
 10 | dan    | C     | NY
 11 | mark   | C     | boston
 12 | linda  | B     | NY
 13 | max    | C     | NY
(13 rows)
 

In order to get what we need we will use the GROUP BY clause:

SELECT grade, city from game GROUP BY grade, city HAVING COUNT(*) > 1;
 
 grade |  city  
-------+--------
 A     | boston
 B     | boston
 C     | NY
(3 rows)
 

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:

SELECT G1.* from game G1 JOIN 
 (select grade, city from game group by grade, city having count(*) > 1) AS G2
using (grade, city) order by grade, city;
 
  id |  name  | grade |  city  
----+--------+-------+--------
  1 | john   | A     | boston
  2 | mike   | A     | boston
  7 | mary   | B     | boston
  8 | van    | B     | boston
 13 | max    | C     | NY
 10 | dan    | C     | NY
  5 | carrie | C     | NY
(7 rows)
 
 

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