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