CODEWITHSUNDEEP
sql
Harsh
Harsh

Reputation: 1

Not able to use ROWNUM in sql developer

I was to fetch all the table details through a query which said:

select * from student3 where rownum <40;

I'm using sql developer for the same, but it is giving an error which says: Invalid column name 'rownum'

The connection that I'm running this query is MSSQL.

I have also tried using the keywords ROW_NUM, ROW_NUMBER But still it says invalid column name

So I'm expecting just to limit the number of rows in the query result upto a certain number

Upvotes: 0

Views: 161

Answers (1)

Govind
Govind

Reputation: 274

You can try like this

CREATE TABLE tblSample(
id INT IDENTITY(1,1) PRIMARY KEY,
firstName NVARCHAR(50),
grade int
)
INSERT INTO tblSample(firstName,grade) VALUES
('Alex',1),
('Max',2),
('Ram',3),
('Shyam',1),
('Deep',2)

;WITH SampleCTE AS 
(
    select   
             RowNum = row_number() OVER ( order by id ),*
    from     tblSample 

)
SELECT  *
FROM    SampleCTE
WHERE   RowNum < 4

DROP TABLE IF EXISTS tblSample;

enter image description here

Upvotes: 1

Related Questions