How To Do Equivalent Of "limit Distinct"?

How can I limit a result set to n distinct values of a given column(s), where the actual number of rows may be higher? Input table: client_id, employer_id, other_value 1, 2, abc 1,

Solution 1:

You can use a subselect

select*fromtablewhere client_id in 
(selectdistinct client_id fromtableorderby client_id limit 5)

Solution 2:

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

SELECT
     client_id,
     employer_id,
     other_value
FROM
     MyTable
WHERE
     client_id IN
     (
          SELECT TOP 5
               client_id
          FROM
          (
               SELECTDISTINCT
                    client_id
               FROM
                    MyTable
          ) SQ
          ORDERBY
               client_id
     )

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

Another possibility (compare performance and see which works out better) is:

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

Solution 3:

-- Using Common Table Expression in Microsoft SQL Server. -- LIMIT function does not exist in MS SQL.

WITH CTE
AS
(SELECTDISTINCT([COLUMN_NAME])
FROM [TABLE_NAME])
SELECT TOP (5) [[COLUMN_NAME]]
FROM CTE;

Solution 4:

This works for ‍‍MS SQL if anyone is on that platform:

SET ROWCOUNT 10;
SELECTDISTINCT
     column1, column2, column3,...
FROM 
     Table1
WHERE ...