This site uses cookies for analytics, personalized content and ads. By continuing to browse this site, you agree to this use.
The official source of product insight from the Visual Studio Engineering Team
Related Resources
Getting Started Resources
Language options
Recent Posts
Archives &(1)情景对话_百度文库
您的浏览器Javascript被禁用，需开启后体验完整功能，
享专业文档下载特权
&赠共享文档下载特权
&10W篇文档免费专享
&每天抽奖多种福利
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
阅读已结束，下载本文需要
想免费下载本文？
定制HR最喜欢的简历
下载文档到电脑，同时保存到云知识，更方便管理
加入VIP
还剩5页未读，
定制HR最喜欢的简历
你可能喜欢sql - ROW_NUMBER() in MySQL - Stack Overflow
to customize your list.
This site uses cookies to deliver our services and to show you relevant ads and job listings.
By using our site, you acknowledge that you have read and understand our , , and our .
Your use of Stack Overflow’s Products and Services, including the Stack Overflow Network, is subject to these policies and terms.
Join Stack Overflow to learn, share knowledge, and build your career.
or sign in with
Is there a nice way in MySQL to replicate the SQL Server function ROW_NUMBER()?
For example:
col1, col2,
ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow
FROM Table1
Then I could, for example, add a condition to limit intRow to 1 to get a single row with the highest col3 for each (col1, col2) pair.
12.3k43558
8,446103244
I want the row with the single highest col3 for each (col1, col2) pair.
That's a , one of the most commonly-asked SQL questions (since it seems like it should be easy, but actually it kind of isn't).
I often plump for a null-self-join:
SELECT t0.col3
FROM table AS t0
LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3&t0.col3
WHERE t1.col1 IS NULL;
“Get the rows in the table for which no other row with matching col1,col2 has a higher col3.” (You will notice this and most other groupwise-maximum solutions will return multiple rows if more than one row has the same col1,col2,col3. If that's a problem you may need some post-processing.)
427k86549754
There is no ranking functionality in MySQL.
The closest you can get is to use a variable:
SELECT t.*,
@rownum := @rownum + 1 AS rank
FROM YOUR_TABLE t,
(SELECT @rownum := 0) r
so how would that work in my case? I'd need two variables, one for each of col1 and col2? Col2 would need resetting somehow when col1 changed..?
If it were Oracle, you could use the LEAD function to peak at the next value.
Thankfully, Quassnoi covers .
245k56430459
I always end up following this pattern. Given this table:
+------+------+
+------+------+
+------+------+
You can get this result:
+------+------+------------+
j | row_number |
+------+------+------------+
+------+------+------------+
By running this query, which doesn't need any variable defined:
SELECT a.i, a.j, count(*) as row_number FROM test a
JOIN test b ON a.i = b.i AND a.j &= b.j
GROUP BY a.i, a.j
Hope that helps!
33.2k1372102
@i:=@i+1 AS iterator,
tablename AS t,
(SELECT @i:=0) AS foo
9,3481786129
Check out this Article, it shows how to mimic SQL ROW_NUMBER() with a partition by in MySQL.
I ran into this very same scenario in a WordPress Implementation. I needed ROW_NUMBER() and it wasn't there.
The example in the article is using a single partition by field. To partition by additional fields you could do something like this:
@row_num := IF(@prev_value=concat_ws('',t.col1,t.col2),@row_num+1,1) AS RowNumber
,@prev_value := concat_ws('',t.col1,t.col2)
FROM table1 t,
(SELECT @row_num := 1) x,
(SELECT @prev_value := '') y
ORDER BY t.col1,t.col2,t.col3,t.col4
Using concat_ws handles null's. I tested this against 3 fields using an int, date, and varchar. Hope this helps. Check out the article as it breaks this query down and explains it.
I would also vote for Mosty Mostacho's solution with minor modification to his query code:
SELECT a.i, a.j, (
SELECT count(*) from test b where a.j &= b.j AND a.i = b.i
) AS row_number FROM test a
Which will give the same result:
+------+------+------------+
j | row_number |
+------+------+------------+
+------+------+------------+
for the table:
+------+------+
+------+------+
+------+------+
With the only difference that the query doesn't use JOIN and GROUP BY, relying on nested select instead.
I would define a function:
delimiter $$
DROP FUNCTION IF EXISTS `getFakeId`$$
CREATE FUNCTION `getFakeId`() RETURNS int(11)
DETERMINISTIC
return if(@fakeId, @fakeId:=@fakeId+1, @fakeId:=1);
then I could do:
select getFakeId() as id, t.* from table t, (select @fakeId:=0) as t2;
Now you don't have a subquery, which you can't have in views.
There is no funtion like rownum, row_num() in MySQL but the way around is like below:
@s:=@s+1 serial_no,
from my_table tbl, (select @s:=0)
From MySQL 8.0.0 and above you could natively use windowed functions.
Window functions.
MySQL now supports window functions that, for each row from a query, perform a calculation using rows related to that row. These include functions such as RANK(), LAG(), and NTILE(). In addition, several existing aggregate functions now can be used for example, SUM() and AVG().
Returns the number of the current row within its partition. Rows numbers range from 1 to the number of partition rows.
ORDER BY affects the order in which rows are numbered. Without ORDER BY, row numbering is indeterminate.
CREATE TABLE Table1(
id INT AUTO_INCREMENT PRIMARY KEY, col1 INT,col2 INT, col3 TEXT);
INSERT INTO Table1(col1, col2, col3)
VALUES (1,1,'a'),(1,1,'b'),(1,1,'c'),
(2,1,'x'),(2,1,'y'),(2,2,'z');
col1, col2,col3,
ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow
FROM Table1;
query for row_number in mysql
set @row_number=0;
select (@row_number := @row_number +1) as num,id,name from sbs
3,85051933
The solution I found to work the best was using a subquery like this:
col1, col2,
SELECT COUNT(*)
FROM Table1
WHERE col1 = t1.col1
AND col2 = t1.col2
AND col3 & t1.col3
) AS intRow
FROM Table1 t1
The PARTITION BY columns just get compared with '=' and separated by AND.
The ORDER BY columns would be compared with '&' or '>', and separated by OR.
I've found this to be very flexible, even if it is a little bit costly.
The rownumber functionality can't be mimicked. You might get the results you expect, but you'll most likely get disappointed at some stage.
Here's what mysql documentation says:
For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:
SELECT @a, @a:=@a+1, ...;
However, the order of evaluation for expressions involving user variables is undefined.
MariaDB 10.2 is implementing "Window Functions", including RANK(), ROW_NUMBER() and several other things:
Based on a talk at Percona Live this month, they are reasonably well optimized.
The syntax is identical to the code in the Question.
58.4k54786
A bit late but may also help to someone who looks for answers...
Between rows/row_number example - recursive query that may be used in any SQL:
WITH data(row_num, some_val) AS
SELECT 1 row_num, 1 some_val FROM any_table --dual in Oracle
SELECT row_num+1, some_val+row_num FROM data WHERE row_num & 20 -- any number
SELECT * FROM data
WHERE row_num BETWEEN 5 AND 10
-------------------
3,73111416
This allows the same functionality that ROW_NUMBER() AND PARTITION BY provides to be achieved in MySQL
@row_num := IF(@prev_value=GENDER,@row_num+1,1) AS RowNumber
FirstName,
@prev_value := GENDER
FROM Person,
(SELECT @row_num := 1) x,
(SELECT @prev_value := '') y
ORDER BY Gender, Age DESC
Also a bit late but today I had the same need so I did search on Google and finally a simple
general approach found here in Pinal Dave's article
I wanted to focus on Paul's original question (that was my problem as well) so I summarize my solution as a working example.
Beacuse we want to partition over two column I would create a SET variable during the iteration to identify if a new group was started.
SELECT col1, col2, col3 FROM (
SELECT col1, col2, col3,
@n := CASE WHEN @v = MAKE_SET(3, col1, col2)
THEN @n + 1 -- if we are in the same group
ELSE 1 -- next group starts so we reset the counter
END AS row_number,
@v := MAKE_SET(3, col1, col2) -- we store the current value for next iteration
FROM Table1, (SELECT @n := 0, @v := NULL) r -- helper table for iteration with startup values
ORDER BY col1, col2, col3 DESC -- because we want the row with maximum value
) x WHERE row_number = 1 -- and here we select exactly the wanted row from each group
The 3 means at the first parameter of MAKE_SET that I want both value in the SET (3=1|2).
Of course if we do not have two or more columns constructing the groups we can eliminate the MAKE_SET operation. The construction is exactly the same. This is working for me as required. Many thanks to Pinal Dave for his clear demonstration.
I don't see any simple answer covering the "PARTITION BY" part so here's mine :
CASE WHEN @partitionBy_1 = l THEN @row_number:=@row_number+1 ELSE @row_number:=1 END AS i
, @partitionBy_1:=l AS p
select @row_number:=0,@partitionBy_1:=null
cross join (
select 1 as n, 'a' as l
select 1 as n, 'b' as l
select 2 as n, 'b' as l
select 2 as n, 'a' as l
select 3 as n, 'a' as l
select 3 as n, 'b' as l
ORDER BY l, n
where i & 1
The ORDER BY clause must reflect your ROW_NUMBER need. Thus there's already a clear limitation: you can't have several ROW_NUMBER "emulation" of this form at the same time.
The order of the "computed column" matters. If you have mysql compute those column in another order, it might not work.
In this simple example I only put one but you can have several "PARTITION BY" parts
CASE WHEN @partitionBy_1 = part1 AND @partitionBy_2 = part2 [...] THEN @row_number:=@row_number+1 ELSE @row_number:=1 END AS i
, @partitionBy_1:=part1 AS P1
, @partitionBy_2:=part2 AS P2
SELECT @row_number:=0,@partitionBy_1:=null,@partitionBy_2:=null[...]
This could also be a solution:
SET @row_number = 0;
(@row_number:=@row_number + 1) AS num, firstName, lastName
1,38431318
set @i = 1;
INSERT INTO ARG_VALUE_LOOKUP(ARG_VALUE_LOOKUP_ID,ARGUMENT_NAME,VALUE,DESCRIPTION,UPDATE_TIMESTAMP,UPDATE_USER,VER_NBR,OBJ_ID)
select @i:= @i+1 as ARG_VALUE_LOOKUP_ID,ARGUMENT_NAME,VALUE,DESCRIPTION,CURRENT_TIMESTAMP,'admin',1,UUID()
FROM TEMP_ARG_VALUE_LOOKUP
order by ARGUMENT_NAME;
181k45276341
This Work perfectly for me to create RowNumber when we have more than one column.
In this case two column.
SELECT @row_num := IF(@prev_value= concat(`Fk_Business_Unit_Code`,`NetIQ_Job_Code`), @row_num+1, 1) AS RowNumber,
`Fk_Business_Unit_Code`,
`NetIQ_Job_Code`,
`Supervisor_Name`,
@prev_value := concat(`Fk_Business_Unit_Code`,`NetIQ_Job_Code`)
FROM (SELECT DISTINCT `Fk_Business_Unit_Code`,`NetIQ_Job_Code`,`Supervisor_Name`
FROM Employee
ORDER BY `Fk_Business_Unit_Code`, `NetIQ_Job_Code`, `Supervisor_Name` DESC) z,
(SELECT @row_num := 1) x,
(SELECT @prev_value := '') y
ORDER BY `Fk_Business_Unit_Code`, `NetIQ_Job_Code`,`Supervisor_Name` DESC
1,53011731
col1, col2,
count(*) as intRow
FROM Table1
GROUP BY col1,col2
ORDER BY col3 desc
2,01841830
Your Answer
Sign up or
Sign up using Google
Sign up using Facebook
Post as a guest
Post as a guest
Post Your Answer
By clicking &Post Your Answer&, you acknowledge that you have read our updated ,
and , and that your continued use of the website is subject to these policies.
Not the answer you're looking for?
Browse other questions tagged
Stack Overflow works best with JavaScript enabledPartners In Arms
In their quest for realism, military shooters have ventured into murky moral territory.
By && August 13, 2012
Ever since a graduate student claiming to be The Joker allegedly shot dozens of moviegoers at a midnight screening of The Dark Knight Rises, the debate about gun control has been co-opted by those attempting to connect or disconnect the dots between the fiction of costumed comic book characters gunning for each other in Christopher Nolan&s violent opus and the very real slaughter that left 12 people dead. Video games have been mentioned mostly by proxy in the conversation (the movie theater gunman was reportedly , not Call Of Duty), but this might also be an opportune moment for the industry to reexamine its long love affair with the modern military shooter and attempts to blur the line between violent video game fantasy and reality.
Take, for instance, the rather overt case of Electronic Arts& Medal Of Honor: Warfighter. Companies often toss around buzzwords like &immersion& or &authenticity& to promote their video games, but EA&s claim that they&ll && for the upcoming sequel to Medal Of Honor doesn&t just smack of hacky marketing speak. First, there&s the promise of ripped-from-the-headlines settings and characters in Warfighter&you&ll be battling the Islamic separatist group Abu Sayyaf and the Somalia-based cell of al-Qaeda in &real-life hotspots& around the world. But EA takes the realism factor further by allowing players to test out a photorealistic replica of, for example, the TAC-300 sniper rifle. Like the way the gun drops terrorists or racks up headshots in multiplayer? Feel free to visit
and click on a sponsored link that will take you to McMillan, the manufacturer of the gun. There you may
to your own specification (night-vision kit is optional!) and have it shipped to your local federally licensed gun dealer for pickup.
There are a host of other guns, knives, scopes, and weapon accessory companies listed as &partners& on . (There are 11 listed at time of writing, and EA says they&re revealing a new partner each week.) In October, you&ll be able to purchase a limited edition
for $75 from SOG Knives that features &an extended cutting head.& It&s certainly a bit more intense than, say, the pewter dragon from the Skyrim Collector&s Edition.
In EA&s defense, the company has created , a program that promises to donate money from Medal Of Honor-themed merchandise sold in their partners& respective stores to charities like the Navy SEAL Foundation and the Special Operations Warrior Foundation. Honoring the military is a worthy cause, but EA is still doing so by promoting weapons that lead to
To be honest, military shooters crossed into ethically murky waters even before Medal Of Honor&s blatant gun advertising. That&s weird for me to write because I&ve always toed the libertarian line on this issue. To me, guns and video games are irrevocably intermeshed like the electronic guitar and rock music. I&ve personally killed hundreds of thousands of people in games (and have the Xbox achievements to prove it) with rifles, pistols, missile launchers, machetes, chainsaws, and gravity guns. I&ve rarely batted an eyelash in response to all of the virtual brutality. But I was also lucky enough to grow up in an era when dissociating the onscreen violence from anything in real life was easy. I was weaned on 8-bit shooters like Ikari Warriors and Contra&both of which portray military operations about as accurately as Super Mario Bros. demonstrates life as a plumber in Brooklyn. In those early games, bullets pop slowly out of pistols like slow-moving golf balls, and enemies don&t &die& as much as they flash briefly before vanishing.
The technological sophistication of military shooters has increased over the years to the point where the guns look awfully close to weapons used in real battlefields and&&uncanny valley& issues aside&enemy soldiers resemble real human beings. When we shoot them, they don&t flicker out of existence. They scream and bleed and die. Of course, the realism that military shooters portray tends to be an aesthetic one. The shooting galleries of Medal Of Honor and Call Of Duty aren&t much like real soldiering&as pointed out by the Onion parody &.&
But try telling that to my 13-year-old nephew, who got kicked out of school after getting caught with a semi-automatic BB gun in his backpack. It&s not a coincidence that the gun resembles his favorite weapon from Modern Warfare 3. Call Of Duty has fostered an obsession with all things guns and military for him. He can rattle off obscure details about the clip size and firing range of assault rifles, and he says he wants to serve as a Navy SEAL after graduating high school. Sure, his parents shouldn&t have let him start playing M-rated military shooters at age 10, but listening to all of the prepubescent squeals during a usual multiplayer match, I know he&s not the exception to the rule.
I can&t say for certain whether or not my nephew would have brought a gun to school without the role of military video games, nor can I say if gun sales will increase because of Medal Of Honor: Warfighter. But if we want the vicarious thrills of violent video games to remain morally justifiable, we need to protect the fourth wall between the first-person shooter and real life. EA&s willingness to make a connection between a video game gun and an actual firearm is the strongest evidence yet that we&ve already let the wall crumble too much.
on TWITTER
on FACEBOOK
Custom Search