Skip to content
Advertisement

Retrieving rows that have different values for a column in unnormalized table

Given the following fruit table, I wanted to write sql query that returns Fruits that have rating of both A and B. In the example, the answer should give Orange and Lemon since they have both A & B ratings. Apple & Pear, though they have A and B rating, they should not be included in the result as they don’t contain both. One way to go about is to use stored procedure, but I wanted to write sql statement that is extensible to any number of ratings (not only A & B).

Expected result:

Advertisement

Answer

This works in Oracle. It requires support for DISTINCT in window functions.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement