Greetings. There is something like this:

<?php foreach ($users as $user) { $sql = 'SELECT age FROM free_people WHERE user_id = ' .$user['id']. ' AND age > 0 ORDER BY photo_id DESC LIMIT 1'; $user_age = $db->select_first($sql); echo $user_age. '&nbsp;лет <br>'; }

In the array $ users for example 30 entries. That is, in this code there will be 30 calls to the database. I wanted to somehow do it all in one request. After all, $ user ['id'] can be combined into a string and stuffed into WHERE IN (...), but this option will output all entries by a specific id, but I need one entry, the first one sorted by photo_id (ORDER BY photo_id DESC).

"Simply put," the algorithm is as follows: take an id, select all records by it, sort it, return the first record, take a trace. id

    2 answers 2

     SELECT F.user_id, F.age FROM free_people F JOIN ( SELECT user_id, max(photo_id) photo_id FROM free_people WHERE user_id IN(1,2,3,4) AND age > 0 GROUP BY user_id ) X ON F.user_id=X.user_id and F.photo_id=X.photo_id

    In the X subquery, we get the maximum photo_id for each user of interest and connect the result with the main table again, getting the records for the given user and photo_id .

    Sqlfiddle example

      If nothing messed up, something like that. The point is to select all the records, sort them, and then just group them. Immediately we can not group, we will select the record with a random photo_id, and we need the greatest, then we do in 2 steps:

       SELECT * FROM ( SELECT p.age, p.user_id FROM free_people p WHERE p.user_id in (' .$userlist. ') AND p.age > 0 ORDER BY p.photo_id DESC) b GROUP BY b.user_id

      Online test

      By the way, why not just take the maximum age?

       SELECT max(age), user_id FROM free_people WHERE user_id in (' .$userlist. ') AND p.age > 0 GROUP BY user_id
      • Damn, here's the option with the maximum age that makes everything straightforward. Thank you very much! - Artem
      • But if we still talk about the subject of the question, for example, for other conditions (where the max function is not appropriate), then the first option has already been tried before and the results are different from those expected. <br/> <br/> For verification, I added a date field to the sample in order to understand whether the first row of the sort is returned or not. As it turned out, the record is returned from the center of the entire sample. That is, a certain id has 6 entries and not the first entry is returned to the group, but 3 or 4 (I didn’t pay attention to it). - Artem
      • @ArtyomKostenko is the reason why we need to sort out first, and only then group it - Crantisz
      • so in your version like and so sorted, and then added to the group, but the result is equivalent to such a query: SELECT age, user_id FROM free_people WHERE user_id IN (...) AND age > 0 GROUP BY user_id - Artyom
      • Кстати, а почему-бы просто не взять максимальный возраст? - because we must take not the maximum age, but the age where the maximum photo_id is - Alexey Shimansky