Solved

MysqlQueryConversiion

Posted on 2011-09-24
6
317 Views
Last Modified: 2012-05-12
How would you write this oracle query in mysql? Does it have psedcolumn rownum assigned to resultset like oracle.

select * from
(select x.*, rownum rn from
(select * from your table
order by some_column) x
where rownum <=  100000)
where rn = 1 or mod(rn,500) in (0,499)

0
Comment
Question by:sam15
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 36593457
I don't understand the mod stuff fully but here is how to do a rownum in mysql:
http://blog.gomilko.com/2007/04/28/mysql-rownum-imitation
0
 

Author Comment

by:sam15
ID: 36593534
It does not seem straightforwardto use rownum in MySQL.

MOD is the remainder function of N divide by m

http://www.techonthenet.com/oracle/functions/mod.php

I hope they have similar one in mysql.

The query above select rows 1,499,500,999,1000,etc.. in a table of 100,000 records.
If there an aleternative sql to get that in mysql
0
 
LVL 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 200 total points
ID: 36593570
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36593573
Yes, MySQL can do a MOD and the link has how to do the row number. There is not the pseudo rownum available, but you can do a good job emulating it. Here is an additional explanation:

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html

MOD => http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_mod
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 300 total points
ID: 36593587
Here, since aarontomosky is on mobile and am familiar with the ranking approach:

select * 
from (
   select x.*, @rownum:=@rownum+1 as rn
   from your_table x, (select @rownum:=0) r
   order by some_column
) derived
where rn = 1
or (rn <= 100000 and mod(rn, 500) in (0, 499))
;

Open in new window

0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 36593596
Please mark a complete written out answer as the solution. If I helped, just give me assist points. This way the marked answer is complete pastable code.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question