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

    Unanswered: how to select 2 from every blah

    Hi,
    This is probably really obvious but I am trying to craft a query which will return 2 rows for every set of criteria.

    For example,
    select name, department from users

    If I just add 'limit 2', then I get 2 total rows when what I really want is 2 users from every department (doesn't really matter which).

    For departments with < 2 users, I would ideally like to have at least 1.

    Any help is appreciated.
    Thanks

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Once again, what version are you running?

  3. #3
    Join Date
    Feb 2010
    Posts
    40
    Sorry, version 8.4.1

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Code:
    SELECT department, name
    FROM (
      SELECT department, name, 
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY name) AS rn
      FROM users
    ) sub
    WHERE sub.rn < 3

  5. #5
    Join Date
    Feb 2010
    Posts
    40
    Thanks! That did the trick.

Posting Permissions

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