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

    Unanswered: help with distinct and best case attempt

    Hi,
    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,
    dbrk.user,
    dbrk.hasphone
    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.

    Any ideas?
    Thanks

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

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