SQL LIMIT by id column

Question

I have the following statement:

SELECT s.*, u.`fname`, u.`lname`, aif.`airport`
FROM services s
INNER JOIN airports_in_flight aif
    ON s.`id` = aif.`service_id`
INNER JOIN users u
    ON s.`service_provider_id` = u.`id`

Lets say for example that this is the result I am getting:

id | ----- | ------ | ...............
 1 |  Jack |  Jones | ...............
 1 |  Moses|  Cohen | ...............
 2 |  Tom  |  Jones | ...............
 3 |  Luke | SkyWal | ...............

So I need to use LIMIT 2 so that I will get the first 3 rows (and not only the first 2). So the Limit should be based on the id column. It should work also when using OFFSET.

How can this be done?

Do all rows in `services` have matching rows in `users` and `airports_in_flight`? If so, you can subselect the necessary `services.id` as a first step. (But not if some of them have to be discarded in the join) — Thilo, Jan 26 '15 at 05:19

Ed King · Accepted Answer · 2015-01-27T16:25:14.657

Instead of reading the services table directly, use a sub query and join to that.

select s.*, u.`fname`, u.`lname`, aif.`airport` FROM 
    ( select * from services order by id limit 2) as s INNER JOIN airports_in_flight aif
    ON s.`id` = aif.`service_id` INNER JOIN users u
    ON s.`service_provider_id` = u.`id`

Depending on your version of mysql, you can't have a subquery IN using limit and offset (This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery') but this will still work.

EDIT:

If you set the inner query to this:

(SELECT s.id
FROM services s
INNER JOIN airports_in_flight aif
    ON s.`id` = aif.`service_id`
INNER JOIN users u
    ON s.`service_provider_id` = u.`id`
LIMIT 2)

Then it will only return services with airports_in_flight and users (possibly add a distinct clause).

EDIT to clarify:

Right now you have this as your select:

select s.*, u.`fname`, u.`lname`, aif.`airport` FROM 
        services as s INNER JOIN airports_in_flight aif
        ON s.`id` = aif.`service_id` INNER JOIN users u
        ON s.`service_provider_id` = u.`id`

You want to limit to 2 services (I don't know if you want all services, I'm guessing just the ones with matching users and airports in flight), so you need to put the limit on the right table.

In this case, the right table is the services table. To make things simple lets start with a simplified version of what you have:

SELECT s.*, aif.`airport` FROM 
        services as s 
INNER JOIN airports_in_flight aif ON s.`id` = aif.`service_id`

I'm going to make an assumption that there is an airport in flight row for every service (we can add that complexity later).

To list the first 2 services, we want the limit on the services, not the whole query, so it would be:

SELECT s.*, u.`fname`, u.`lname`, aif.`airport` FROM 
        (select * from services limit 2) as s 
INNER JOIN airports_in_flight aif ON s.`id` = aif.`service_id`

Notice I replaced the table services with a query, that I can now limit, this is the subquery. If we now want to only look at services that have an airport in flight, we need to change that subquery from:

select * from services limit 2

to

select ss.* from services ss 
inner join airports_in_flight aifs on ss.`in` = aifs.`service_id` limit 2

I've renamed the services and airports_in_flight tables ss and aifs so they don't collide with the names in the main query, put an inner join in to limit my rows to only service table, and limited by 2, so putting the subquery into the query we now get:

select s.*, u.`fname`, u.`lname`, aif.`airport` 
FROM 
  (select ss.* from services ss 
   inner join airports_in_flight aifs on ss.`in` = aifs.`service_id`
   limit 2) as s 
INNER JOIN airports_in_flight aif ON s.`id` = aif.`service_id`

You should be able to then expand the subquery to add the inner join for users (thus limiting services to only those that have airports_in_flight rows and users rows), and add the users table to the main query.

the problem I see with this is that the criteria for the ID's that are wanted is not enforced. The original query has a criteria for which ID's are wanted. The inner select must preserve that criteria. — Jose Martinez, Jan 26 '15 at 05:57
You can tweak the inner select for whatever criteria you need -- I'm assuming a unique id in the services table, this will give records with 2 service id types. What criteria would not be enforced? I'm assuming we want to limit/offset the service ids. — Ed King, Jan 26 '15 at 05:59
@EdKing thank you for the help, but this does not solve the issue. when I use **LIMIT 2** I get first two rows, while what i am expecting is the first 3 since the id's there are 1,1 and 2 (which means limit 2). — Gil404, Jan 27 '15 at 14:16
@Gil404 -- does the edit I made make it clearer? The key is, limit the subquery (whatever you choose that to be) not the main query. — Ed King, Jan 27 '15 at 16:26

SQL LIMIT by id column

1 Answers1

Linked