Skip to content

Instantly share code, notes, and snippets.

@lichtner

lichtner/row-number.sql

Last active July 11, 2016 15:07
Show Gist options
  • Save lichtner/208f4e04a0ff54d8b65e7f300111e487 to your computer and use it in GitHub Desktop.
Save lichtner/208f4e04a0ff54d8b65e7f300111e487 to your computer and use it in GitHub Desktop.
Download ZIP
Implement ROW_NUMBER() for mysql, maria if this window functions does not exists
Raw
row-number.sql
### http://www.mysqltutorial.org/mysql-row_number/
# simple row number
set @row_number = 0;
select (@row_number := @row_number + 1) AS num, id
FROM Term;
# select row number for each group -----------------------------
set @row_number = 0;
set @year = null;
select
@row_number:=CASE
WHEN @year = vsYear THEN @row_number + 1
ELSE 1
END AS num,
@year := vsYear as vsYear,
id, dateFrom, courseId
FROM Term
WHERE vsYear <> 16
order by vsYear, dateFrom
# update -----------------------------------------
set @row_number = 0;
set @year = null;
update Term
set vsTerm = @row_number:=CASE
WHEN @year = vsYear THEN @row_number + 1
ELSE 1
END,
vsYear = @year := vsYear
WHERE vsYear <> 16
order by vsYear, dateFrom;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment