CODEWITHSUNDEEP
sqlsql-server
Alex Brown
Alex Brown

Reputation: 123

Paging in sql server for top level query

In sql server I have an Employee table and an Address table.
An Employee can have many Addresses.
I want to get the first 10 Employees with their Addresses.

SELECT * 
FROM
  Employee e 
  LEFT JOIN Address a ON a.EmployeeID = e.Id
WHERE
  a.Street LIKE '%a%'
  AND e.Name LIKE '%bob%'
ORDER BY e.Id 
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

If each employee has 2 addresses associated with it I will get 10 rows but only 5 employees.
How do I get the 10 employees I want?

Upvotes: 1

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can use dense_rank():

SELECT ea.*
FROM (SELECT e.*, a.*,  -- should select the columns you really need
             DENSE_RANK() OVER (ORDER BY e.id) as seqnum
      FROM Employee e  JOIN
           Address a
           ON a.EmployeeID = e.Id
      WHERE a.Street LIKE '%a%' AND e.Name LIKE '%bob%'
     ) ea
WHERE seqnum <= 10
ORDER BY e.Id ;

Upvotes: 1

Related Questions