CODEWITHSUNDEEP
phpmysqlsql
Poorman
Poorman

Reputation: 205

Select highest id given in the list

I have a list of multiple ids (ex. (5,8,9)).

I would like to find in a table the highest datas with the ids the list.

ex. In ma table I have some records with the highest id is 8 (9 doesn't exist in this table). So it needs to return all records where id_link = 8.

the id list is obviously dynamic.

How can I make this request ?

Other ex:

My table :

id | id_link | name

1  |    5    | name_1
2  |    8    | name_2
3  |    8    | name_3
4  |    8    | name_4

In my request, I would like to give this list of ids : (5,8,9).

So it should return me all values with the highest existing id in the list.

In this case all rows with id_link = 8

Upvotes: 0

Views: 190

Answers (2)

forpas
forpas

Reputation: 164154

This query:

select max(id_link) from tablename
where id_link in (5, 8, 9)

returns the max of the id_links in the list that exists in your table.
So use it like this: 

select *
from tablename
where id_link = (
  select max(id_link) from tablename
  where id_link in (5, 8, 9)
)

See the demo.
Results:

| id  | id_link | name   |
| --- | ------- | ------ |
| 2   | 8       | name_2 |
| 3   | 8       | name_3 |
| 4   | 8       | name_4 |

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562691

You can search for your three values, and they may or may not match any rows. As you said, there is no row with id 23, so that will not return anything. But you can still include the value in the search.

Then return the MAX(id) from those values that were found.

SELECT MAX(id) FROM MyTable WHERE id IN (8, 12, 23);

If you want the whole row, not just the MAX(id) you can do this:

SELECT id, ... FROM MyTable WHERE id IN (8, 12, 23)
ORDER BY id DESC LIMIT 1;

Upvotes: 0

Related Questions