Is there a "First" command in SQL?

[softwareguy74]

In access, I can add the First command to a select query in order to pull the first occurance of a unique record, but there does not seem to be a First command in SQL Server. Does anyone know the equivalent?

So for example, say I have the following data:

0001 | Dan Bassett | This is a test
0001 | Dan Bassett | This is another test

If I were to do a Select on this, both records would return, but how would I limit it to just the first record?

[Hack]

A SELECT DISTINCT should do the trick for you...

[Sheppe]

Originally posted by Hack
A SELECT DISTINCT should do the trick for you...

I believe the Distinct keyword is row-based, and it would view those two rows as distinct entries.

Try a "Select Top 1 * From Tablename... Where..." and so on.

[Hack]

You are correct Sheppe, the DISTINCT keyword is row based.

My assumption was that that was the basis for the question. Your code works very well if I was incorrect.

[Mc Brain]

Anyway... SELECT DISTINCT UserID, UserName From Table would also do the trick.

[Sheppe]

Originally posted by Mc Brain
Anyway... SELECT DISTINCT UserID, UserName From Table would also do the trick.

Not if the rows themselves are distinct, as in his example.

[Mc Brain]

The Id and name are the same in his example. Obviously... if he doesn't add a WHERE clause, it will retrieve all the users (but, only once each)

[Sheppe]

Originally posted by Mc Brain
The Id and name are the same in his example. Obviously... if he doesn't add a WHERE clause, it will retrieve all the users (but, only once each)

Yeah, but judging by his example, I'd say he wants all three columns returned, not just the two that you are selecting.

[Mc Brain]

Ok, in that case... only the TOP 1 would work for him.

[Sheppe]

Originally posted by Mc Brain
Ok, in that case... only the TOP 1 would work for him.

Technically he could also do the following:

Set rowcount 1
Select ....
Set rowcount 0

But the Top 1 makes more sense.