1

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:enter image description here

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

enter image description here

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.

LHC2012
  • 187
  • 6
  • `ORDER BY` is the last operation performed, so you get the same results as the first table, but sorted by the `nm` column. You may need to `SELECT` from a subquery that orders the results before populating the `rownumber` column. – sj95126 Sep 18 '22 at 23:42
  • You are probably after [window functions](https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html) and `ROW_NUMBER()` as a function. – danblack Sep 19 '22 at 00:15
  • Don't use variables (`@` sign). They are deprecated and supported in MySQL 5.7; this version of MySQL is reaching end of life in 13 months from now. – The Impaler Sep 19 '22 at 02:58
  • It is that way, because that is how you are calculating the rownumber. What is the purpose of the rownumber – Rohit Gupta Sep 19 '22 at 11:04

1 Answers1

3

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.

Lisa
  • 76
  • 6