Applying SQL limit on a particular column, not on complete result set

Question

In the below given resultset where there are 2 unique users (id) and there could be more such users coming out of the query:

Here is the multi-join query:

select 
    id, name, col1Code, col2Code, col2Value 
from 
    users_table UT
join 
    col1_table C1T on UT.id = C1T.user_ID_FK
join 
    col2_table C2T on UT.id = C2T.user_ID_FK
order by 
    UT.id 
limit 10

Let's say there are 50 such users from this query. If I apply limit of 10 for pagination purposes, I won't be getting 10 Users, but will be only getting the first 10 rows which may have only 1 or 2 users data.

Q1: How do I put limit based upon a particular column?

Q2: Is using the SQL limit clause the correct approach for pagination, also for improving the response time for end-user? If not then what else can be used in this scenario?

first you need to define how your Expected Results will always spit out. Have you done that above or is that still up in the air and left for confusion? — Drew, Aug 15 '16 at 12:25
Possible duplicate of [Using LIMIT within GROUP BY to get N results per group?](http://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) — Tim Biegeleisen, Aug 15 '16 at 12:26
Since I need all Col1 and Col2 values for all users, I will not know the num of rows that each User will fetch out. so in all, I would need the complete resultset that has 10 Unique User ids. Am I still sounding confused?? — Bhavya Narula, Aug 15 '16 at 12:46

score 0 · Answer 1 · answered Aug 15 '16 at 12:26

Q1: You can use a subquery to grab distinct users.

select id, name, col1Code, col2Code, col2Value 
from 
 (select distinct id, name from users_table) UT
 join col1_table C1T on UT.id = C1T.user_ID_FK
 join col2_table C2T on UT.id = C2T.user_ID_FK
 order by UT.id limit 10

Q2: Yes

Applying SQL limit on a particular column, not on complete result set

1 Answers1