1

enter image description here

I need a query that will return distinct rows, but will not return any rows that have duplicate fruits where one of the rows is 'Approved'. Note: there will always be at most two fruits of the same name.

For example, the ideal result would be:

enter image description here

What I have so far is simply:

SELECT DISTINCT * FROM Food;

But this obviously still returns one of the rows that has the fruit 'Apple'

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
nullptr
  • 147
  • 3
  • 13
  • 1
    Can be done in several ways. Start with a `NOT EXISTS` query, try `GROUP BY` with `HAVING` etc. – jarlh May 23 '18 at 19:20
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry May 23 '18 at 20:25

3 Answers3

2

You can make use of some conditional aggregation for the desired result set like 

select fruit, min(status)
from demo
group by fruit
having sum(status = 'Approved') = 0
and sum(status = 'Bought') > 0

or just use select fruit, 'Bought' as status

Demo

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
0
SELECT DISTINCT * FROM Food WHERE Fruit NOT IN (SELECT (UNIQUE Fruit) FROM Food WHERE Status = 'Approved');

Try this query.

ragav ramachandran
  • 368
  • 2
  • 8
-1

I think you could solve this problem a different way perhaps? To get that same result you could add a WHERE clause, for instance:

SELECT DISTINCT * FROM Food WHERE Status = 'Bought';

You could also alter the data model so that a fruit's status is updated rather than adding another record for the same fruit (if that suits the application's purposes).

If you want to maintain the schema and get the same, I believe you can do: SELECT DISTINCT on (Fruit) Fruit, Status FROM Food ORDER BY Fruit, Status ASC;

Source

Tom Con
  • 620
  • 7
  • 12