By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,393 Members | 1,405 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,393 IT Pros & Developers. It's quick & easy.

equivalent Oracle rownum in SQLServer

P: n/a
Hello,

I would like to know if the equivalent Oracle rownum exist in
SQLServer. Here is a sample SQL code to explain what I want to do :

select
jobs.name,
jobs.job_id,
jobs.description,
hist.message,
hist.step_name,
hist.step_id,
hist.run_status,
hist.run_date,
hist.run_time,
hist.run_duration
from
msdb.dbo.sysjobs jobs,
msdb.dbo.sysjobhistory hist
where
jobs.job_id=hist.job_id
and hist.job_id='E71CCB97-81C3-46E2-83FA-BFFCB66B47F8'
order by
run_date, run_time

I just want the first or second row returned by this query. In Oracle I
can simply add rownum=1 or rownum=2 in the where clause to obtain the
desired answer. I don't know how to do in SQLServer.

Thank in advance,
Pierig.

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

pierig.gueg...@gmail.com wrote:
Hello,

I would like to know if the equivalent Oracle rownum exist in
SQLServer. Here is a sample SQL code to explain what I want to do :

select
jobs.name,
jobs.job_id,
jobs.description,
hist.message,
hist.step_name,
hist.step_id,
hist.run_status,
hist.run_date,
hist.run_time,
hist.run_duration
from
msdb.dbo.sysjobs jobs,
msdb.dbo.sysjobhistory hist
where
jobs.job_id=hist.job_id
and hist.job_id='E71CCB97-81C3-46E2-83FA-BFFCB66B47F8'
order by
run_date, run_time

I just want the first or second row returned by this query. In Oracle I can simply add rownum=1 or rownum=2 in the where clause to obtain the
desired answer. I don't know how to do in SQLServer.

Thank in advance,
Pierig.


Jul 23 '05 #2

P: n/a
Use the 'top n' clause

eg. select top 5
jobs.name,
jobs.job_id,
jobs.description,
hist.message, ...........

Jul 23 '05 #3

P: n/a
See TOP in Books Online:

select top 2 jobs.name, jobs.job_id, ...

Note that TOP doesn't take a variable, so if you need that flexibility,
you would need to use SET ROWCOUNT, which can use a variable.

Simon

Jul 23 '05 #4

P: n/a
You need to get a book on basic RDBMS design. Tables are sets, not
files. They have no ordering. The Oracle row numbers merely violate
Codd's rule #8 and tell us that under the covers, Oracle is a
sequential contigous file system. But it leads you contiue to create
file systems in SQL.

I see by the use of a GUID, another exposed physical locator, for a key
that you do not have a relational design at all. You also split date
and time into two columns than added a computed column called duration,
so you have NFNF problems.

You need to start over.

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.