CODEWITHSUNDEEP
mysqlsqldatabase
JoshuaDavid
JoshuaDavid

Reputation: 9519

SQL to give X records after specified record

Is it possible to write a SQL statement that retrieves X consecutive records after a record with criteria Y, from a list with criteria Z?

for example, given this table :

id  name
------------------------------
1   aaa
5   bbb
10  ccc
15  ddd
20  eee
25  fff
30  ggg

I first apply criteria Z, something like

SELECT * WHERE (id>4) AND (id<26) ORDER BY id ASC

then I am left with a list:

id  name
------------------------------
5   bbb
10  ccc
15  ddd
20  eee
25  fff

I want to know if it's possible to retrieve 2 records from this list from where name='ddd' (or some other criteria Y), ie, to return the "ddd" and "eee" records in the above example.

It must possible to do this directly in SQL, but I'm afraid I lack the mileage to know it.

Upvotes: 1

Views: 190

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

SELECT * 
FROM yourTable
WHERE id > 4 
  AND id < 26 
  AND id >= ( SELECT min(id) 
              FROM yourTable
              WHERE id > 4 
                AND id < 26 
                AND name = 'ddd'
            ) 
ORDER BY id 
LIMIT 2
;

In your wording:

SELECT * 
FROM yourTable
WHERE ( criterionZ )
  AND sortingField >=
            ( SELECT min(sortingField ) 
              FROM yourTable
              WHERE ( criterionZ )
                AND ( criterionY )
            ) 
ORDER BY sortingField
LIMIT X
;

Upvotes: 2

Finbar Crago
Finbar Crago

Reputation: 444

Making the presumption your id numbers are sequential you can use a subquery:

SELECT * FROM  table WHERE id >= (SELECT id FROM table WHERE name =  'ddd' LIMIT 1) LIMIT 2

Upvotes: 1

Nicola Cossu
Nicola Cossu

Reputation: 56357

Add limit clause

SELECT * WHERE (id>4) AND (id<26) ORDER BY id ASC limit 2,2

edit.

select * from table where id > (
select id from table where name = 'eee' ) 
order by id
limit 2

Upvotes: 1

Related Questions