How to select the line with the maximum id where in the page column will be poll

I tried this SELECT MAX(id) FROM wall

Everything is good, but I need to select the line where in the page parameter will be poll

  • It is strange that such an obvious question has not yet met on the site ... I did not find a suitable closing. - AK

3 answers 3

You wanted to give an answer to your question, it apparently solves your specific problem. But it minus, and the other answer - plus. What's the matter?

A programmer is clarity of mind and accuracy of formulations.

If you formulated the task as "find the row identifier with the maximum ...", then your answer would brilliantly fit the task conditions. Yes, it happens that some beginners do not know about the existence of the where construct and ask about it. Such an answer would be sufficient and complete:

 SELECT MAX(id) FROM wall WHERE page='poll' 

But .. you wrote "How to choose a line with the maximum" and even carried it to the headline. And this (in your opinion) insignificant change radically changes the essence of the question (for respondents).

From your design you will not go on to select the entire line or part of the line fields. But the answer @Naumov in this regard is beautiful, accurate and does exactly what is asked:

 SELECT * FROM `wall` WHERE `page` = "poll" ORDER BY `id` DESC LIMIT 1 

In general, why did I undertake to write the answer, although there are already two answers to two questions at once? The point is this.

If you have two servers, the sql server is running on one and the client on the other (and even if it is the same server), then our task is to save the RAM buffers in memory through which the server sends the data to the client ( transfer two fields of the table over the network or twenty-two - there is a difference), save traffic over the network (transfer one line or a million lines) and the number of connections (this time, brakes).

Why do you need this ID? The site user does not need an ID, he needs other table fields. The most unwise is to get this max (id) from the server on the client and immediately get into the following query to select all the fields SELECT * FROM table WHERE id = received id.

Even such a bulky query will be better in terms of saving connections and reducing the total running time:

 SELECT * FROM table WHERE id = (SELECT MAX(id) FROM wall WHERE page='poll') 

But this is a less optimal request than the answer that Naumov already gave you, you can see the execution plan for the request through explain.

Therefore, I recommend accepting its version, not yours. Alternatively, one could rewrite the question wording and accept it. But I am not sure that your hunt will bother so much - and most importantly, I think that for future visitors to leave just such a version of the question, because it is precisely for such requests that they will google afterwards.

Total, small summary:

It is desirable to clearly understand what you need: either only id or the whole line.

I hope the information from my answer will help to make a more informed choice.

  • I hope correctly corrected? - Qwertiy
  • @Qwertiy Thank you - AK
 SELECT * FROM `wall` WHERE `page` = "poll" ORDER BY `id` DESC LIMIT 1 

The logic is easy to choose, sort in descending order and take the 1st row.

  • And a light touch - to impose where page= 'poll' . Add, it seems to me that the topic starter does not understand the basics of the query language. - AK
  • one
    descending .... - teran
  • @teran I just always confuse "decreasing" with "increasing", "dinner" with "breakfast" is such a feature. - Naumov

If it is necessary to get only the identifier of the string , then such a construction will also work:

 SELECT MAX(id) FROM wall WHERE page='poll' 
  • 2
    Since when does this construct select the entire line? - Qwertiy
  • It looks like an answer (recommending the deletion does not work out - it “looks fine”), but the answer is incorrect. You have a minus from me, I'll take it off if you correct the answer to the worker. - AK