Query help using top

1

Hi ,
I want help of one oracle query,which is need to convert int SQL server.

CREATE TABLE TEST_TB
(
MOB_NO INT,
OTP_PIN INT,
OTP_USED INT,
SNO_PK INT,
GEN_DATE DATETIME2 DEFAULT GETDATE()
);

Insert into TEST_TB (MOB_NO,OTP_PIN,OTP_USED,SNO_PK,GEN_DATE) values (1234,323,324234,1,'2017-11-23 19:45:03.3630000');
Insert into TEST_TB (MOB_NO,OTP_PIN,OTP_USED,SNO_PK,GEN_DATE) values (2323,234,7887686,2,'2017-11-23 19:45:20.8430000');
Insert into TEST_TB (MOB_NO,OTP_PIN,OTP_USED,SNO_PK,GEN_DATE) values (8787,8989789,546546,3,'2017-11-23 19:45:29.8430000');
Insert into TEST_TB (MOB_NO,OTP_PIN,OTP_USED,SNO_PK,GEN_DATE) values (1234,232,324234,4,'2017-11-23 19:51:09.0870000');
Insert into TEST_TB (MOB_NO,OTP_PIN,OTP_USED,SNO_PK,GEN_DATE) values (1234,555,8888,5,'2017-11-23 19:51:22.7300000');

QUERY

SELECT * FROM
(
SELECT
MOB_NO as MOBILENO,
OTP_PIN as OTPPIN
FROM
TEST_TB
WHERE
MOB_NO =1234
ORDER BY
GEN_DATE DESC
)
WHERE ROWNUM<=?;

2

Something like this otta do it:

SELECT * FROM
(
SELECT
MOB_NO as MOBILENO,
OTP_PIN as OTPPIN,
RN = ROW_NUMBER() OVER(ORDER BY GenDate)
FROM
TEST_TB
WHERE
MOB_NO =1234
ORDER BY
GEN_DATE DESC
)
WHERE rn < ?
3

Hi,
Thanks for the reply ,I am getting below error while executing the query.

SQL Error [1033] [S1000]: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Please help

4

Move the ORDER BY out of the sub query

You'll need the ORDER BY columns (i.e. GEN_DATE) in the SELECT list for the sub query.

5

More precisely, the ORDER BY in the OVER() clause does the trick. This should work:

SELECT * FROM
(
SELECT
MOB_NO as MOBILENO,
OTP_PIN as OTPPIN,
RN = ROW_NUMBER() OVER(ORDER BY GenDate DESC)
FROM
TEST_TB
WHERE
MOB_NO =1234
)
WHERE rn < ?
6

Doh! Indubitably Sorry about that ... :slight_smile:

Cold add ORDER BY RN at the bottom if same ordering as ROW_NUMBER() OVER() is required for the report