MySQL - Check for multiple same values in one row

Question

I have a MySQL-table with the following structure for songs in a chartlist.

ID - Title - Artist - Releaseyear - 1999 - 2000 - 2001 - 2002 - 2003 - ..etc.. - 2013.

The columns '1999', '2000' etc are the positions of the song in the chart of that year.

Now I want to check which songs are on the same place in at least three years.

I've found queries for finding duplicates on multiple rows, but not yet for this case.

What a sucky schema. In 2 days will you have to rewrite all your queries to know about the 2014 column? — Barmar
– Barmar, Commented Dec 31, 2013 at 9:41
@Barmar: No, it is an annual chart. I can simply add the 2014 position in december 2014. :) — Pino112
– Pino112, Commented Dec 31, 2013 at 9:56
That still means you have to update all your queries every year. Data that varies should be in column values, not the schema. — Barmar
– Barmar, Commented Dec 31, 2013 at 9:58

Barmar · Accepted Answer · 2013-12-31 09:50:08Z

3

Use a UNION to convert the columns to rows, then you can find duplicate rows.

SELECT ID, place, GROUP_CONCAT(year ORDER BY year) years
FROM ( -- Pivot the table to get all the years and places in the same column
    SELECT ID, "2001" AS year, `2001` AS place
    UNION
    SELECT ID, "2002" AS year, `2001` AS place
    UNION
    ...
    UNION
    SELECT ID, "2013" AS year, `2013` AS place
    ) AS byYear
GROUP BY ID, place
HAVING COUNT(*) >= 3

answered Dec 31, 2013 at 9:50

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL - Check for multiple same values in one row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related