Solved

MysqlQueryConversiion

Posted on 2011-09-24
6
315 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
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 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

740 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