equivalent Oracle rownum in SQLServer

[pierig.gueguen]

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.

[deebeeay]

[e-mail] 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.

[deebeeay]

Use the 'top n' clause

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

[Simon Hayes]

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

[--CELKO--]

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.