如何选择mysql结果？

Question

invoice

+----+-----+---------+-------+
| Sr | BRN |  Name   |  Amnt |
+----+-----+---------+-------+
| 1  | 1   | John    |  10   |
| 2  | 1   | John    |   4   |
| 3  | 2   | Belly   |   4   |
| 4  | 3   | John    |  14   |
| 5  | 4   | John    |   5   |
| 6  | 4   | John    |  14   |
+----+-----+---------+-------+

I want to select all rows except the duplicate BRN. (If there are two/more ge in BRN then it should only select one)

I tried:

SELECT *(DISTINCT BRN) FROM invoice

Expected result:

+-----+---------+-------+
| BRN |  Name   |  Amnt |
+-----+---------+-------+
| 1   | John    |  10   |
| 2   | Belly   |   4   |
| 3   | John    |  14   |
| 4   | John    |   5   |
+-----+---------+-------+

Answer 1

Given the following table:

+----+-----+---------+-------+
| Sr | BRN |  Name   |  Amnt |
+----+-----+---------+-------+
| 1  | 1   | John    |  10   |
| 2  | 1   | John    |   4   |
| 3  | 2   | Belly   |   4   |
| 4  | 3   | John    |  14   |
| 5  | 4   | John    |   5   |
| 6  | 4   | John    |  14   |
+----+-----+---------+-------+

with the expected results:

+-----+---------+-------+
| BRN |  Name   |  Amnt |
+-----+---------+-------+
| 1   | John    |  10   |
| 2   | Belly   |   4   |
| 3   | John    |  14   |
| 4   | John    |   5   |
+-----+---------+-------+

The difficult part is getting the amount, because it is arbitrary, not to mention that the values in Amnt are pretty much worthless in this result.

If you want distinct BRN, the query would be SELECT DISTINCT BRN FROM invoice

You might even get away with SELECT DISTINCT BRN, Name FROM invoice

An intermediate step would be SELECT BRN,Name FROM invoice GROUP BY BRN, Name

But if you try to include Amnt in the equation, then the query will fail because there's no way for the database to determine which Amnt to show.

So, you could try this kludge:

SELECT a.BRN, a.Name, b.Amnt FROM invoice AS a LEFT JOIN invoice AS b ON a.BRN=b.BRN

No guarantees on which Amnt it will pick up, though.

Hope that helps.