Clever Geek Handbook

How do you make the equivalent of a "limit"?

How can I limit the result set to n distinct values ​​for a given column (s), where the actual number of rows might be higher?

Input table:

client_id, employer_id, other_value
1, 2, abc
1, 3, defg
2, 3, dkfjh
3, 1, ldkfjkj
4, 4, dlkfjk
4, 5, 342
4, 6, dkj
5, 1, dlkfj
6, 1, 34kjf
7, 7, 34kjf
8, 6, lkjkj
8, 7, 23kj

desired output, where limit distinct = 5 distinct client_id values:

1, 2, abc
1, 3, defg
2, 3, dkfjh
3, 1, ldkfjkj
4, 4, dlkfjk
4, 5, 342
4, 6, dkj
5, 1, dlkfj

The platform is designed for MySQL.

+1


source to share


2 answers


You can use a subheading



select * from table where client_id in 
(select distinct client_id from table order by client_id limit 5)
+11


source


This is for SQL Server. I don't remember MySQL can use the LIMIT keyword instead of TOP. This can make the query more efficient if you can get rid of the inner subquery itself using LIMIT and DISTINCT in the same subquery. (It looks like Vinko used this method and that LIMIT is correct. I'll leave that here for a second possible answer, though.)

SELECT
     client_id,
     employer_id,
     other_value
FROM
     MyTable
WHERE
     client_id IN
     (
          SELECT TOP 5
               client_id
          FROM
          (
               SELECT DISTINCT
                    client_id
               FROM
                    MyTable
          ) SQ
          ORDER BY
               client_id
     )

Of course add in your own WHERE clause and ORDER BY clause in the subquery.



Another possibility (compare performance and see which one comes out better):

SELECT
     client_id,
     employer_id,
     other_value
FROM
     MyTable T1
WHERE
     T1.code IN
     (
          SELECT
               T2.code
          FROM
               MyTable T2
          WHERE
               (SELECT COUNT(*) FROM MyTable T3 WHERE T3,code < T2.code) < 5
     )
0


source






More articles:


All Articles