mySQLi / PHP从所有匹配的行中选择具有最高索引/ id的行[duplicate]

Question

This question already has an answer here:

• Select last row in MySQL 10 answers

How can I select the row with the highest id from all rows where the query conditions match?

for example this query:

SELECT * FROM tbl_pictures WHERE user='1' AND type='profilepic'

So if user1 already uploaded a few profile pictures, there will be couple rows which match the query. Since my tbl_pictures has an auto increment id i always want to get only the row which matches the query and has the highest index compared to the other matches.

How do I have to write that query?

</div>

Answer 1

SELECT * FROM tbl_pictures WHERE user='1' AND type='profilepic' ORDER BY `id` DESC LIMIT 1

Edit:

Explaining for Hallur:

ORDER BY is used to (guess what) - order the results of the query by the following column. After the column you can optionally specify ASC or DESC to order in ascending or descending order. You can optionally add more sorting condition, separating them with comma like: ORDER BY field1 ASC, field2 DESC, field3.

The LIMIT clause is used to limit the results of the query. You can use LIMIT number to return up to number results, or you can use LIMIT offset, number to indicate from which record to start (or how many records to skip) and from then on - how many to return.

Hope it's more clear :)