CODEWITHSUNDEEP
mysqlsql
learner
learner

Reputation: 4818

Get min from second column if first column is equal in SQL

CREATE TABLE EMPLOYEE (
  empId INTEGER Not NULL,
  id1 INTEGER NOT NULL,
  id2 INTEGER NOT NULL,
  id3 INTEGER NOT NULL
);

-- insert
INSERT INTO EMPLOYEE VALUES (0001, 1, 10,100);
INSERT INTO EMPLOYEE VALUES (0001, 2, 10,100);
INSERT INTO EMPLOYEE VALUES (0002, 3, 10,100);
INSERT INTO EMPLOYEE VALUES (0002, 3, 11,100);
INSERT INTO EMPLOYEE VALUES (0003, 1, 12,101);
INSERT INTO EMPLOYEE VALUES (0003, 1, 12,102);


-- fetch 
select * from EMPLOYEE where (empId,(id1)) in (SELECT empId,min(id1) FROM EMPLOYEE WHERE 1=1 group by empId);

This query gives me:

empId   id1 id2 id3
1   1   10  100
2   3   10  100
2   3   11  100
3   1   12  101
3   1   12  102

What I need is:

empId   id1 id2 id3
1   1   10  100
2   3   11  100
3   1   12  102

So, I am looking for min of id2, if id1 is equal, id3 if both id1 and id2 are equal. How can I achieve this using a query?

Upvotes: 1

Views: 229

Answers (1)

Jon Armstrong
Jon Armstrong

Reputation: 4694

The fiddle (updated)

Note: The first query requires MySQL 8.0 or better. For older versions, a slightly different solution is required. See the second query to support older versions too.

I believe your expected result is wrong, in that when id1, id2 are the same for empId = 3, 101 is the MIN id3 value, not 102. The following should do what you asked, however, using the ROW_NUMBER window function.

For each empId, assign a row number for each row based on (id1, id2, id3) being the least weighted tuple, from left to right, then choose just the first row number (rn = 1) in each partition.

WITH cte AS (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY empId ORDER BY id1, id2, id3) AS rn
          FROM EMPLOYEE
     )
SELECT * FROM cte WHERE rn = 1
;

For older versions too (this doesn't produce an rn column in the result):

SELECT e1.*
  FROM      EMPLOYEE AS e1
  LEFT JOIN EMPLOYEE AS e2
    ON e1.empId = e2.empId
   AND (e1.id1, e1.id2, e1.id3) > (e2.id1, e2.id2, e2.id3)
 WHERE e2.empId IS NULL
;

The result:

empId id1 id2 id3 rn
1 1 10 100 1
2 3 10 100 1
3 1 12 101 1

If we want the MAX id3 (instead of MIN), for those matching cases, simply reverse the id3 logic:

WITH cte AS (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY empId ORDER BY id1, id2, id3 DESC) AS rn
          FROM EMPLOYEE
     )
SELECT * FROM cte WHERE rn = 1
;

-- and

SELECT e1.*
  FROM      EMPLOYEE AS e1
  LEFT JOIN EMPLOYEE AS e2
    ON e1.empId = e2.empId
   AND (e1.id1, e1.id2, -e1.id3) > (e2.id1, e2.id2, -e2.id3)
 WHERE e2.empId IS NULL
;

The result:

empId id1 id2 id3 rn
1 1 10 100 1
2 3 10 100 1
3 1 12 102 1

Upvotes: 1

Related Questions