Issue

This Content is from Stack Overflow. Question asked by LHC2012

I am beginner in sql and this is the mysql code I was testing.

CREATE TABLE test (
    id INT,
    nm VARCHAR(16),
    occ VARCHAR(16)
);

INSERT INTO test (id, nm, occ)
VALUES (1, "E", "X"), (2, "B", "X"), (3, "C", "Y"), (4, "D", "Z"), (5, "A", "Z");

SET @r1 = 0, @r2 = 0, @r3 = 0;

SELECT *,
CASE WHEN occ = 'X' THEN (@r1 := @r1 + 1)
     WHEN occ = 'Y' THEN (@r2 := @r2 + 1)
     WHEN occ = 'Z' THEN (@r3 := @r3 + 1) END AS rownumber,
CASE WHEN occ = 'X' THEN nm END AS X,
CASE WHEN occ = 'Y' THEN nm END AS Y,
CASE WHEN occ = 'Z' THEN nm END AS Z
FROM test
ORDER BY nm;

My question is as follows. If I remove the ORDER BY nm clause what I get is this table below:

But if I add the clause I get the following table:

Why is the rownumber 1 in the row with nm = 'A' in the second table after applying ORDER BY nm? I would think it is 2 because I thought SELECT takes precedence over ORDER BY.

Solution

This question has been asked before. You can check this out, so then maybe can help you to understand it.

I can help you to read what the meaning of the rownumber from your code. First, let’s understand what’s the total value of each rownumber.

SELECT occ, count(occ) FROM test GROUP BY occ ORDER BY occ;

This is another code from your first result

SELECT *, ROW_NUMBER() OVER(PARTITION BY occ) AS rownumber, CASE WHEN occ = 'X' THEN nm END AS X, CASE WHEN occ = 'Y' THEN nm END AS Y, CASE WHEN occ = 'Z' THEN nm END AS Z FROM test;

And this is how will I show you your two results CMIIW. You can compare the row number for each nm value with occ value.
When nm is E then X first appears in the first result, it’s 1 so then nm B has rownumber is 2. Unlike from the second result, when nm is B then X first appears its 1 so then nm E with X has rownumber is 2.

There is has another function to get row number in MYSQL but it’s just for MySQL 8.0 or later. You can read for ROW_NUMBER() function here and RANK() function here.

This Question was asked in StackOverflow by LHC2012 and Answered by hlyza18 It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.