Solved

MysqlQueryConversiion

Posted on 2011-09-24
6
313 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
  • 3
  • 2
6 Comments
 
LVL 38

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 38

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 200 total points
ID: 36593570
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 59

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 59

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 38

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

803 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