some departments where there are at least 3 people with phones would give me a result with all three users having phones.
The general format of my query is like
FROM (select rank() over (partition by dp.name order by u.name) rk,
dp.name as department,
u.name as user,
(case when asset.name = 'Phone' then 1 else 0 end) as hasphone
FROM user u
LEFT JOIN 'bunch of stuff'...
ORDER by dp.name, d.name, hasphone desc ) dbrk
WHERE dbrk.rk < 3
The problem is I get several rows returned per user. So I decided to change my subselect to get distinct rows
FROM (select distinct on (dp.name, u.name) rank() over (partition by dp.name order by u.name) rk, ....
but there is now no guarantee I will first get users with phones ahead of users without phones. I figured if I could sort with my hasphones desc first, then distinct might sift off the top.
Hmm, but how do I select just N users from department making sure the ones I select are more likely to have a phone? This query seems like it will just return me the number of phones assigned to each user.