Solved

how to change the query from oracle to  mysql.

Posted on 2016-10-05
3
39 Views
Last Modified: 2016-10-06
below query has been written in oracle.how to change this query in mysql.

select *
               from (
                    select rownum as rn, a.*
                    from (select * from test
                            where test_datetime  <= #{testDate}
                          order by test_datetime asc) a
                          )
              where rownum <= #{pageSize} and rn > #{offset}
0
Comment
Question by:chaitu chaitu
  • 2
3 Comments
 
LVL 32

Expert Comment

by:ste5an
ID: 41829830
It's exactly the same in MySQL.

The interesting part is: In what front-end do you use? Cause the parameter format looks pretty suspicious.
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 41829842
java.
Is rownum  there in mysql?
0
 
LVL 32

Accepted Solution

by:
ste5an earned 500 total points
ID: 41829858
Ah, I see. In this case you need an iterator:

SELECT T.*, 
       @rownum := @rownum + 1 AS rownum
  FROM yourTable T, 
       (SELECT @rownum := 0) R

Open in new window


E.g. untested
SELECT  * ,
		@rownum := @rownum + 1 AS rownumO
FROM    ( SELECT	@rownum := @rownum + 1 AS rownumI ,
                    A.*
          FROM      ( SELECT    *
                      FROM      test
                      WHERE     test_datetime <= '#{testDate}'
                      ORDER BY  test_datetime ASC
                    ) A,
					 (SELECT @rownum := 0) RI
        ) B ,
		(SELECT @rownum := 0) RO
WHERE   rownumI <= '#{pageSize}'
        AND rownumO > '#{offset} '

Open in new window

0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

759 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

21 Experts available now in Live!

Get 1:1 Help Now