CODEWITHSUNDEEP
mysqlsql
oodavid
oodavid

Reputation: 2326

Select first row but only if there's more than one match?

Given the table `users` below:

+----+---------+--------+
| id | name    | office |
+----+---------+--------+
|  1 | David   |      1 |
|  2 | Roz     |      1 |
|  3 | Patrick |      2 |
|  4 | Chris   |      3 |
|  5 | Agnes   |      3 |
|  6 | Freya   |      3 |
+----+---------+--------+

I want to select the first user of any given office, but ONLY if there's more than one user, so:

Something along the lines of:

SET @office_id = 2;
SELECT *
  FROM `users`
WHERE `office` = @office_id AND number-of-users-for-office > 1
ORDER BY `id` ASC
LIMIT 1;

Upvotes: 0

Views: 4773

Answers (3)

oodavid
oodavid

Reputation: 2326

I came up with another solution that I'm jotting for posterity.

-- Office id...
SET @office_id = 1;
-- Find the latest user
SELECT `id` INTO @latest_user_id
    FROM `users`
    WHERE `office` = @office_id
    ORDER BY `id` DESC
LIMIT 1;
-- Find the first user that isn't the first
SELECT `id` INTO @latest_user_id
    FROM `users`
    WHERE `office` = @office_id AND `id` != @latest_user_id
    ORDER BY `id` ASC
LIMIT 1;

SQL Fiddle Demo

This one selects the latest, then tries to select the first where it's not the same as the latest. Thus if there's only 1 row you'll get NULL as desired.

Upvotes: 0

Shafeeque
Shafeeque

Reputation: 2069

EDIT

SELECT `office`,if(count(`id`)>1,name,'NULL') as name
FROM (SELECT * FROM `tablename` ORDER BY id ASC)
`tablename`
GROUP BY `office`
ORDER BY `id` ASC

Upvotes: -1

John Woo
John Woo

Reputation: 263723

SELECT  a.office, 
        MAX(
        CASE WHEN b.ID IS NULL
            THEN NULL
            ELSE a.Name
        END) Name
FROM    Tablename a
        LEFT JOIN
        (
            SELECT  office, MIN(id) ID
            FROM    Tablename
            GROUP   BY office
            HAVING  COUNT(*) > 1
        ) b ON  a.office = b.office AND
                a.ID = b.ID
-- WHERE .......                       -- (if you have extra conditions)
GROUP   BY a.office

OUTPUT

╔════════╦════════╗
║ OFFICE ║  NAME  ║
╠════════╬════════╣
║      1 ║ David  ║
║      2 ║ (NULL) ║
║      3 ║ Chris  ║
╚════════╩════════╝

The purpose of the subquery is to get the least ID for every Office. The extra HAVING clause filters only records that has more than one employee within the certain office.

Table User is then joined on the subquery via LEFT JOIN to get all the office within the table. The records are aggregated using MAX() (or MIN()) to get single record for every office.

Upvotes: 3

Related Questions