OraFAQ Forum: Server Administration » Is there a query for this case?

Home » RDBMS Server » Server Administration » Is there a query for this case?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Is there a query for this case? [message #371749]

Fri, 01 December 2000 12:48

John Armani
Messages: 3
Registered: December 2000

Junior Member

Say I have a table with the following data:
Id Grade
-- -----
1 A
2 B
3 C
4 A
5 B
6 A
7 D

I would like a query to return all id/grade combinations where there are multiple ids with the
same grade (and order by Grade). The query would return the following from the above table...

Id Grade
-- -----
1 A
4 A
6 A
2 B
5 B
Is it possible through a single query? Any clues would be greatly appreciated.

John
armani@armani.rochny.uspra.abb.com

Report message to a moderator

Re: Is there a query for this case? [message #371750 is a reply to message #371749]

Fri, 01 December 2000 14:08

Elena
Messages: 14
Registered: December 2000

Junior Member

SELECT ID, GRADE
FROM TABLE_NAME
ORDER BY GRADE;

Report message to a moderator

Re: Is there a query for this case? [message #371751 is a reply to message #371750]

Fri, 01 December 2000 14:47

John Armani
Messages: 3
Registered: December 2000

Junior Member

This query will return all rows. I want to exclude rows where the grade is referenced by only one id.

Report message to a moderator

Re: Is there a query for this case? [message #371752 is a reply to message #371749]

Fri, 01 December 2000 15:29

Elena
Messages: 14
Registered: December 2000

Junior Member

Is it OK?
SQL> SELECT ID, GRADE
FROM A
WHERE GRADE IN (SELECT GRADE
FROM A
HAVING COUNT(GRADE)>1
GROUP BY GRADE);

ID GRADE
--------- ---
1 A
4 A
6 A
2 B
5 B

Report message to a moderator

Re: Is there a query for this case? [message #371773 is a reply to message #371752]

Mon, 04 December 2000 09:55

John Armani
Messages: 3
Registered: December 2000

Junior Member

Elena - thanks for the help!

Report message to a moderator

Previous Topic:	packages
Next Topic:	get sql_text within trigger

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 02 08:09:40 CDT 2024