Blobonat Blobonat - 6 months ago 14
MySQL Question

Get first/second/third element from sql query

I have a sql query like

SELECT id FROM chart WHERE name='qwertz' AND run_id=3


I know that this query will return 3 datasets. For another complex query I need the first/second/third id that returned my query above like

SELECT value FROM data WHERE id=(SELECT id FROM chart WHERE name='qwertz' AND run_id=3).getFirst

SELECT value FROM data WHERE id=(SELECT id FROM chart WHERE name='qwertz' AND run_id=3).getSecond

SELECT value FROM data WHERE id=(SELECT id FROM chart WHERE name='qwertz' AND run_id=3).getThird


How can I do it?

Answer

You can use OFFSET with LIMIT :

SELECT * FROM chart
WHERE name = 'qwertz' and run_id = 3
LIMIT 1,0 -- First record

SELECT * FROM chart
WHERE name = 'qwertz' and run_id = 3
LIMIT 1,1 -- Second record

SELECT * FROM chart
WHERE name = 'qwertz' and run_id = 3
LIMIT 1,2 -- Third record

Although with out a specific ORDER BY clause, this will be random records!

Can also be written as one query to get all of them together :

SELECT * FROM chart
WHERE name = 'qwertz' and run_id = 3
LIMIT 3 -- First 3