# 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.```