Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010

    Unanswered: help with distinct and best case attempt

    I have a schema of users and departments. Each user has multiple assets several of which can be phones (or they may have no phones at all).

    I am trying to build a query which will return 3 people from every department and attempt to list users who have at least one phone assigned to them first. For example, I might have

    Dept User HavePhone (1=yes)?
    Engineering jsmith 1
    Engineering lsmith 1
    Engineering dsmith 0
    Sales esmith 1
    Sales jdoe 0
    Sales hdoe 0

    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

    SELECT dbrk.department,
    FROM (select rank() over (partition by order by rk, as department, as user,
    (case when = 'Phone' then 1 else 0 end) as hasphone
    FROM user u
    LEFT JOIN 'bunch of stuff'...

    ORDER by,, 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 (, rank() over (partition by order by 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.

    Any ideas?

  2. #2
    Join Date
    Aug 2009
    Olympia, WA
    SELECT AS department, AS employee,
      (SELECT COUNT(1) FROM assets a
       WHERE a.user_id = u.user_id
       AND = 'Phone'
      ) AS phones
    FROM department d
    JOIN user u ON d.dept_id = u.dept_id

  3. #3
    Join Date
    Feb 2010
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts