期望多个结果时正确的SELECT查询

Question

I am trying to come up with the proper query for my problem. Basically, I might have over 100 of the exact same result if I do:

    $query = "SELECT * FROM highway WHERE state  = 'VA'";
    $result = mysql_query($query) or die(mysql_error());
    while ($row = mysql_fetch_array($result)) {
    //code

With this I would get:

    95
    95
    95
    95 (and on and on...)
    64
    64
    64 (and on and on...)

What would be the proper query to just get:

    95
    64
    66
    81

Answer 1

You can use the DISTINCT keyword:

The ALL and DISTINCT options specify whether duplicate rows should be returned. ALL (the default) specifies that all matching rows should be returned, including duplicates. DISTINCT specifies removal of duplicate rows from the result set. It is an error to specify both options. DISTINCTROW is a synonym for DISTINCT.

So:

SELECT DISTINCT * FROM highway WHERE state  = 'VA'

But if your resultset has duplicates when considering certain columns, it seems to me that your database tables are likely poorly designed. Have you considered further normalising them?