ak111in
ak111in

Reputation: 107

Select values which are distinct in a group but not overall

I have a table like below:

    id  title  year
    ---------------
    1    abc   1999
    2    abc   2000
    3    abc   2000
    4    abc   1998
    5    bce   1999
    6    bce   1999
    7    def   1999
    8    def   1999
    9    def   2000

I need to get a list of titles which are distinct for a year but otherwise they can repeat. Tried distinct but it is giving distinct overall not based on year.

Tried

SELECT DISTINCT `title` FROM xyz WHERE year IN (SELECT DISTINCT year FROM xyz)

Current output:

   abc
   bce
   def

Expected output:

   abc
   abc
   abc
   bce
   def
   def

Upvotes: 0

Views: 35

Answers (1)

potashin
potashin

Reputation: 44581

SELECT `title`
FROM `xyz`
GROUP BY `year`
       , `title`
ORDER BY `title

SQLFiddle

Upvotes: 2

Related Questions