Using DISTINCT to filter duplicates?

Question

I have the following query, and would like to list only the first match.

$first = $_GET['category'];
$first = $first[0] . "%";

$query = mysql_query("SELECT * FROM lyrics WHERE authorclean LIKE '".$first."'") or die(mysql_error());

(?category=b)

So DISTINCT could do this right? This is what I tried, but did not work:

$query = mysql_query("SELECT DISTINCT authorclean FROM lyrics WHERE authorclean LIKE '".$first."'") or die(mysql_error());

EDIT: Here is the full code:

function getCategory() {
$first = $_GET['category'];
$first = $first[0] . "%";

$query = mysql_query("SELECT DISTINCT authorclean FROM lyrics WHERE authorclean LIKE 'B%'") or die(mysql_error());
//$query = mysql_query("SELECT * FROM lyrics WHERE authorclean LIKE '".$first."'") or die(mysql_error());
if(mysql_num_rows($query) == 0) {
    echo "Geen resultaten gevonden.";
} else { 
while ($row = mysql_fetch_assoc($query)) { ?>
    <p><a href="/<?= $_GET['category']; ?>/<?= strtolower($row['authorclean']); ?>/"><?= $row['author']; ?></a></p>
    <?php }
    }
}

(B% is just for testing)

If I run this following query in the database directly I get two results. If I run with the code above I just get an empty page (except for the html thats already there).

SELECT DISTINCT authorclean FROM lyrics WHERE authorclean LIKE 'B%'

Answer 1

If you have a a table "tbl_lyrics" with fields: author lyrics year and is filled for example as follows:

author_A       lyrics_A        year_A
author_A       lyrics_A1       year_A1
author_A1       lyrics_A2       year_A
author_B        lyrics_B1       year_B1

if you do

select distinct(author) from tbl_lyrics where author like '%author_A%'

you are going to get: author_A and author_A1. NOT the first one that matches.

If you want the first one that matches you can do:

select author from (select distinct(author) as author from tbl_lyrics where author like '%author_A%') where rownum <2;

this will return author_A only.

Limit is used with MySql but would not work with oracle databases

Answer 2

You should use LIMIT 1 to list only the first match.