RobVious RobVious - 7 months ago 19
SQL Question

Select from table iterating through subset first

I have a query:

select USERNAME, BIRTHDATE, USERID, LASTONLINE, LOCATIONNAME (etc)
from [User]
where AccountDisabled <> 1 (and a whole bunch of other criteria)
order by LastOnline desc offset
@skip rows fetch next 40 rows only


Now, I've been given a list of [User].UserIds that take priority in this search - we want to iterate through these [User]s first, filtering out the users that match the search criteria, and return those as well as the next 40 from the overall [User] table that match.

How can this be done, with performance taken into consideration as a priority?

Answer

The simplest solution is probably UNION ALL:

select USERNAME, BIRTHDATE, USERID, LASTONLINE, LOCATIONNAME (etc) 
 from [User] 
 where AccountDisabled <> 1 (and a whole bunch of other criteria) 
AND USERID IN (list of userids)

UNION ALL

select * from 
 (

    select USERNAME, BIRTHDATE, USERID, LASTONLINE, LOCATIONNAME (etc) 
     from [User] 
     where AccountDisabled <> 1 (and a whole bunch of other criteria) 
    AND USERID NOT IN (list of userids)
     order by LastOnline desc offset
     @skip rows fetch next 40 rows only
 ) as dt

Or you apply some ROW_NUMBER logic like this:

select *
from
 (
    select USERNAME, BIRTHDATE, USERID, LASTONLINE, LOCATIONNAME (etc),
        CASE WHEN USERID IN (list of userids) THEN 0 ELSE 1 END AS grp
        ROW_NUMBER() 
        OVER (PARTITION BY CASE WHEN USERID IN (list of userids) THEN 0 ELSE 1 END 
              ORDER BY LastOnline desc) AS rn
     from [User] 
     where AccountDisabled <> 1 (and a whole bunch of other criteria) 
 ) as dt
 where grp = 0
    or rn BETWEEN @skip AND @skip + 40