CODEWITHSUNDEEP
phpmysqlsql
userzero
userzero

Reputation: 119

Show one row only in same id

I would like to ask how to show one row only in same id, in below example, I just want to show the highest score result in the table.

Here is my code

    $query = "SELECT * FROM memberdata ORDER BY score DESC";
    echo "<table border=1>
    <tr>
    <th> id </th>
    <th> score </th>

    </tr>";

    while($row = mysqli_fetch_array($result)) {

        echo "<tr>";
        echo "<td>" . $row['id'] . "</td>";
        echo "<td>" . $row['score'] . "</td>";
        echo "</tr>";
    }

    echo "</table>";

And the Output is

id score 
1  5000
1  4000
1  3000
1  500
2  3000
2  1000

Upvotes: 1

Views: 2738

Answers (3)

Anik Islam Abhi
Anik Islam Abhi

Reputation: 25352

Try this:

SELECT id, MAX(score) AS score FROM memberdata GROUP BY id

or this:

SELECT * FROM memberdata ORDER BY score DESC LIMIT 1

Upvotes: 3

Jerodev
Jerodev

Reputation: 33186

Use Group by

SELECT id, MAX(score) AS score FROM memberdata GROUP BY id

Upvotes: 4

Rishi Dua
Rishi Dua

Reputation: 2334

You need to iterate to find the maxrow first.

    $query = "SELECT * FROM memberdata ORDER BY score DESC";
    echo "<table border=1>
    <tr>
    <th> id </th>
    <th> score </th>

    </tr>";

    $maxrow = mysqli_fetch_array($result);
    while($row = mysqli_fetch_array($result)) {
        if ($maxrow['score'] > row['score']) maxrow = row;
    }
    echo "<tr>";
    echo "<td>" . $maxrow['id'] . "</td>";
    echo "<td>" . $maxrow['score'] . "</td>";
    echo "</tr>";
    echo "</table>";

Upvotes: 0

Related Questions