2

I just upgraded to MySQL 5.7 and unfortunately for me, some of the functionality of GROUP BY is gone. I wanted to select all movies from my movies table with as long as the movies.id of type int is not a duplicate. My previous query in MySQL 5.6 was:

SELECT *
FROM movies
WHERE movies.title LIKE '%example%'
GROUP BY movies.id

If I had two movies with the same id, it would only display one movie, instead of that movie and its duplicates.

When I upgraded to MySQL 5.7, the GROUP BY gave me errors and I was instead told to use ORDER BY. However this query:

SELECT *
FROM movies
WHERE movies.title LIKE '%example%'
ORDER BY movies.id

Does return duplicate movies. So, is there a way to filter this out, and only return a row if it isn't a duplicate?

Edit: For example if this is my movies table:

movies
==================
|  id  |   title  |
==================
| 1    | example  |
------------------
| 2    | example  |
------------------
| 1    | example  |
------------------

Here is the output of each query:

Previous query result (with MySQL 5.6)
=======
1 | example
2 | example

New query result (with MySQL 5.7 and ORDER BY)
=======
1 | example
1 | example
2 | example

I want the final result to contain no duplicates (so the result should look like the first query result).

Edit 2: I understand I was sort of abusing the way MySQL handled GROUP BY. Unfortunately, I do not have much experience with MySQL and got that answer from StackOverflow. I would just like to return all columns in my table that do not contain duplicate ids.

Alex
  • 2,145
  • 6
  • 36
  • 72
  • Why do you have duplicate movies? If you have no PRIMARY KEY then, as far as the RDBMS is concerned, you have no table. – Strawberry May 18 '16 at 07:40
  • @Strawberry This is for an assignment in school. We were purposefully given bad data for the assignment. :| Unfortunately it is up to us to return non-duplicate rows. Everything did work fine until 5.7.. but I can't downgrade because I need some of the newer features that it provides. – Alex May 18 '16 at 07:41
  • 2
    GROUP BY is intended to work with aggregate functions. SELECT DISTINCT is intended for use where no aggregation is required. – Strawberry May 18 '16 at 07:43
  • @Strawberry From what I understand, I was sort of abusing the way MySQL 5.6 worked, unknowingly (I got the `GROUP BY` thing from an answer on stack overflow). I would really just like to return all rows that do not contain duplicate ids. – Alex May 18 '16 at 07:47
  • The general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function. (Older MySQL versions didn't care about this, and instead returned an arbitrary result.) – jarlh May 18 '16 at 07:49
  • Have you tried `SELECT DISTINCT`? – jarlh May 18 '16 at 07:50
  • @jarlh From my understanding you can not `SELECT DISTINCT` on just one column and still retrieve all of the columns ([found here](http://stackoverflow.com/questions/5967130/mysql-select-one-column-distinct-with-corresponding-other-columns)). If there is a way to `SELECT DISTINCT` on only one column while returning all other columns how would I be able to do that? – Alex May 18 '16 at 07:52
  • Correct, it works on the whole selected rows. In your case you will get two rows, (1, example) and (2, example). Isn't that what you want? – jarlh May 18 '16 at 07:55
  • @jarlh Doing `SELECT DISTINCT * FROM movies WHERE movies.title = 'example'` returns non-distinct rows for some reason. Ie. it returns the duplicate id `1` and the duplicate title `example`. – Alex May 18 '16 at 08:00
  • Are your movies called `example`, or something else...? – jarlh May 18 '16 at 08:02
  • @jarlh It's a search that uses the `LIKE` predicate. So the actual example is `SELECT DISTINCT movies.* FROM movies WHERE movies.title LIKE %term%`. This ends up returning duplicates. Sorry about that, I didn't realize the `LIKE` predicate might affect the distinct results. – Alex May 18 '16 at 08:03
  • LIKE has nothing to do with SELECT DISTINCT. Are you getting duplicate rows when SELECT DISTINCT? – jarlh May 18 '16 at 08:59

1 Answers1

0

I believe would be easy to use distinct keyword 

SELECT distinct movies.*
FROM movies 
WHERE movies.title = 'example'
Pavel Zimogorov
  • 1,387
  • 10
  • 24