CODEWITHSUNDEEP
mysqldistinct
Dean
Dean

Reputation: 517

mysql, select Distinct still returning multile items

I'm trying to do a mysql query to get a Distinct number of values.

my query is 

SELECT DISTINCT * FROM `videos` WHERE `Title` LIKE '%lovelace%'

it's still returning results with the same title,

i tried to do:

SELECT DISTINCT `Title`,* FROM `videos` WHERE `Title` LIKE '%lovelace%'

but it did not work, can anyone help?

I'd like it to return the whole row (*), but with a distinct rule on the title,

regards

Upvotes: 1

Views: 354

Answers (3)

O. Jones
O. Jones

Reputation: 108641

I think you'll find it helpful to create a more rigorous specification of what you require from your query.

Guessing, it seems your videos table contains some duplicates; otherwise SELECT * would do what you need. So, guessing, it seems like you're looking for a query to eliminate duplicates.

The question is, how do you define a duplicate? If you have two rows with the same Title it means you have two listings for that particular video. Presumably you want to display just one of them. Which one? How can distinguish the one you want to display from the one you want to omit?

SELECT DISTINCT is pretty good for eliminating duplicates in a simple way. But it's not very sophisticated. If your table has a primary key, perhaps called id, SELECT DISTINCT * will not find any duplicates, because there aren't any (every id is different from the previous one, of course).

Pro tip: avoid using SELECT * in your programs. Avoid specifications that say you need "everything out of the query." Instead, in your query name the columns you actually need to appear in your result set.

If you do a query like this

    SELECT DISTINCT Title,
                    Director,
                    Year
               FROM videos
              WHERE Title LIKE '%lovelace%'

you may actually get the result you're looking for. But it's hard to help with the actual query because you haven't disclosed the columns in your table.

Upvotes: 0

David Elliman
David Elliman

Reputation: 1389

Use GROUP BY title instead of DISTINCT

Upvotes: 5

Rahul Tripathi
Rahul Tripathi

Reputation: 172398

Try something like this:-

SELECT DISTINCT Title FROM `videos` WHERE `Title` LIKE '%lovelace%'

Edit:-

SELECT * FROM `videos` WHERE `Title` LIKE '%lovelace%' GROUP BY TITLE

Upvotes: 4

Related Questions