How to do an equivelent First() operation

set rowcount 1
select Idnum1,Idnum3 from mytable order by Idnum1 DESC, idnum3 ASC

is equivelent to
select first(Idnum1),first(idnum3) from mytable order by first(Idnum1)
DESC ,first(Idnum3) ASC

Isn't that silly? why did Microsoft not add an agregate function First
for the Microsoft SQL Server to be consistent with its Microsoft Access
product eh?As you are learning, SQL Server has little in common with Access. SQL
Server stays much closer to the relational model. In this case, the
relational model tells us that tables are un-ordered sets; there is no
concept of first or last in sets.

The SQL Server 2005 documentation also says that set rowcount will be
going away some day. We should be using TOP instead, in this case TOP
1. With an ORDER BY, TOP 1 will return the "first" row as determined
by the ORDER BY. Without the ORDER BY the single row returned is
random.

Roy Harvey
Beacon Falls, CT

On 3 May 2006 12:45:58 -0700, rss@.ddsc.com wrote:

>set rowcount 1
>select Idnum1,Idnum3 from mytable order by Idnum1 DESC, idnum3 ASC
>is equivelent to
>select first(Idnum1),first(idnum3) from mytable order by first(Idnum1)
>DESC ,first(Idnum3) ASC
>
>Isn't that silly? why did Microsoft not add an agregate function First
>for the Microsoft SQL Server to be consistent with its Microsoft Access
>product eh?|||rss@.ddsc.com wrote:
> set rowcount 1
> select Idnum1,Idnum3 from mytable order by Idnum1 DESC, idnum3 ASC
> is equivelent to
> select first(Idnum1),first(idnum3) from mytable order by first(Idnum1)
> DESC ,first(Idnum3) ASC
>
> Isn't that silly? why did Microsoft not add an agregate function First
> for the Microsoft SQL Server to be consistent with its Microsoft Access
> product eh?

RANK() and ROW_NUMBER() are much more powerful alternatives. FIRST is
not a proper aggregate function, even in Access. RANK() and
ROW_NUMBER() are also ANSI-standard SQL functions. One would hope that
ANSI compatibility is a higher priority for inclusion than replicating
the peculiar quirks of Access.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:bj2i5256taa324mt0v5umfdbnvc4p1om58@.4ax.com...
> As you are learning, SQL Server has little in common with Access. SQL
> Server stays much closer to the relational model. In this case, the
> relational model tells us that tables are un-ordered sets; there is no
> concept of first or last in sets.
> The SQL Server 2005 documentation also says that set rowcount will be
> going away some day. We should be using TOP instead, in this case TOP
> 1. With an ORDER BY, TOP 1 will return the "first" row as determined
> by the ORDER BY. Without the ORDER BY the single row returned is
> random.

Actually row_number() or rank() is an even better solution.

But agreed, rowcount isn't a good solution here.

> Roy Harvey
> Beacon Falls, CT
> On 3 May 2006 12:45:58 -0700, rss@.ddsc.com wrote:
> >set rowcount 1
> >select Idnum1,Idnum3 from mytable order by Idnum1 DESC, idnum3 ASC
> >is equivelent to
> >select first(Idnum1),first(idnum3) from mytable order by first(Idnum1)
> >DESC ,first(Idnum3) ASC
> >Isn't that silly? why did Microsoft not add an agregate function First
> >for the Microsoft SQL Server to be consistent with its Microsoft Access
> >product eh?