Solved

MysqlQueryConversiion

Posted on 2011-09-24
6
311 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now