0

My query: 

SELECT education,nickname,height
    ROW_NUMBER() OVER (PARTITION BY education ORDER BY height DESC) INDEX_NUM
FROM user
LIMIT 100;

My table definition: 

CREATE TABLE user (

female BOOLEAN NOT NULL,

nickname VARCHAR(60) NOT NULL,
id INT NOT NULL,
charm INT NOT NULL,
standardmember BOOLEAN NOT NULL,
idcard BOOLEAN NOT NULL,

age INT NOT NULL,
marriage VARCHAR(5) NOT NULL,
region VARCHAR(10) NOT NULL,

education VARCHAR(10) NOT NULL,
height INT NOT NULL,
weight INT NOT NULL,
constellation VARCHAR(5),
race VARCHAR(5),
animalzodiac VARCHAR(2) NOT NULL,

intro TEXT NOT NULL,

rage VARCHAR(10) NOT NULL,
rheight VARCHAR(15) NOT NULL,
rrace VARCHAR(10) NOT NULL,
reducation VARCHAR(10) NOT NULL,
rphoto VARCHAR(5) NOT NULL,
rmarriage VARCHAR(10) NOT NULL,
rregion VARCHAR(100) NOT NULL,
rhonesty VARCHAR(10) NOT NULL,

jobtitle VARCHAR(15),
industry VARCHAR(20),
companynature VARCHAR(10),
employeewelfare VARCHAR(10),
workintensity VARCHAR(10),
mobility VARCHAR(10),
careervsfamily VARCHAR(10),
overseaassignment VARCHAR(10),

college VARCHAR(30),
majortype VARCHAR(5),
languages VARCHAR(50),

birthplace VARCHAR(10),
residence VARCHAR(10),
nationality VARCHAR(15),
personality VARCHAR(5),
humor VARCHAR(6),
temper VARCHAR(6),
seriousness VARCHAR(15),
kidwish VARCHAR(5),
whentomarry VARCHAR(5),
longdistancelove VARCHAR(5),
dreammarriage TEXT,

livewithparents VARCHAR(5),
parents VARCHAR(5),
siblings VARCHAR(25),
financialsituation VARCHAR(10),
parentsinsurance VARCHAR(10),
parentsemployment VARCHAR(35),

sent BOOLEAN NOT NULL DEFAULT 0;

PRIMARY KEY (id)

);

My goal is to divide the result set and sort them into a height-ascending order. Like this:

my goal

But I got the error:

syntax error

Well, I'm not so good with MySQL and query syntaxes. Feel to free to teach me if I asked a stupid question. Thx.

underscore_d
  • 6,309
  • 3
  • 38
  • 64
displayName
  • 1
  • 1
  • 3
  • https://stackoverflow.com/questions/1895110/row-number-in-mysql – jarlh Apr 13 '18 at 09:05
  • You should post sample data and errors as text, not images. Text is searchable; images are not (and worse when you just link them off-site). Anyway, I'm interested to know what you thought that syntax would work! – underscore_d Apr 13 '18 at 09:19
  • Sorry, it's my first time asking a question here in StackOverflow. I will follow your advice next time. Thank you! @underscore_d – displayName Apr 13 '18 at 09:24

1 Answers1

1

Try this query:

SELECT education,
       nickname,
       height,
       ROW_NUMBER() OVER w AS INDEX_NUM
FROM u
WINDOW w AS (
             PARTITION BY education,
             nickname ORDER BY height DESC 
             );

Here I used the ROW_NUMBER with Window functions. Since MySQL supports nonaggregate functions that are used only as window functions. For these, the OVER clause is mandatory like:

CUME_DIST()
DENSE_RANK()
FIRST_VALUE()
LAG()
LAST_VALUE()
LEAD()
NTH_VALUE()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

For more info follow the below link:

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

Nishant Gupta
  • 3,533
  • 1
  • 11
  • 18