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;
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
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
When nm is
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
X first appears its 1 so then nm
X has rownumber is 2.