Mysql Unique Query

Question

I have a programme listing database with all the information needed for one programme packed into one table (I should have split programmes and episodes into their own) Now since there are multiple episodes for any given show I wish to display the main page with just the title names in ascending and chosen letter. Now I know how to do the basic query but this is all i know

SELECT DISTINCT title FROM programme_table WHERE title LIKE '$letter%'

I know that works i use it. But I am using a dynamic image loading that requires a series number to return that image full so how do I get the title to be distinct but also load the series number from that title?

I hope I have been clear.

Thanks for any help

Paul

Answer 1

select title,series_number from programme_table group by title,series_number having title like '$letter%';

Answer 2

DISTINCT keyword works actually for a list of colums so if you just add the series to your query it should return a set of unique title, series combinations:

SELECT DISTINCT title, series FROM programme_table WHERE title LIKE '$letter%'

Answer 3

You can substitute the DISTINCT keyword for a GROUP BY clause.

SELECT
  title 
, series_number
FROM 
  programme_table 
WHERE title LIKE '$letter%'
GROUP BY 
  title
, series_number

There are currently two other valid options:
The option suggested by Mohammad is to use a HAVING clause in stead of the WHERE clause this is actually less optimal:

The WHERE clause is used to restrict records, and is also used by the query optimizer to determine which indexes and tables to use. HAVING is a "filter" on the final result set, and is applied after ORDER BY and GROUP BY, so MySQL cannot use it to optimize the query.
So HAVING is a lot less optimal and you should only use it when you cannot use 'WHERE' to get your results.

quosoo points out that the DISTINCT keyword is valid for all listed columns in the query. This is true, but generally people do not recommend it (there is no performance difference In some specific cases there is a performance difference). The MySQL optimizer however spits out the same query for both so there is no actual performance difference.

Update Although MySQL does apply the same optimization to both queries, there is actually a difference: when DISTINCT is used in combination with a LIMIT clause, MySQL stops as soon as it finds enough unique rows. so

SELECT DISTINCT
  title 
, series_number
FROM 
  programme_table 
WHERE 
  title LIKE '$letter%'

is actually the best option.

Answer 4

Hey thanks for that but i have about 1000 entries with the same series so it would single out the series as well rendering about 999 programmes useless and donot show.

I however found out away to make it unique and show the series number

SELECT * FROM four a INNER JOIN (SELECT title, MIN(series) AS MinPid FROM four WHERE title LIKE '$letter%' GROUP BY title) b ON a.title = b.title AND a.series = b.MinPid

Hopefully it helps anyone in the future and thank you for the replies :)