SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-18 : 09:03:15

Shveta writes "How can we make sure that query would return only 1 row?
Which Keyword or Query to be used for this?"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-18 : 09:07:09

There's 2 methods:

SELECT TOP 1 col1, col2 FROM myTable

or...

SET ROWCOUNT 1
SELECT col1, col2 FROM myTable
SET ROWCOUNT 0

The first method uses the TOP keyword to limit how many rows are returned. The second method, ROWCOUNT, sets an internal limit that affects all SQL operations, not just SELECT. Also, you cannot use a dynamic variable with TOP, but you can with ROWCOUNT:

DECLARE @rows int
SET @rows=5
SELECT TOP @rows col1, col2 FROM myTable --won't work

SET ROWCOUNT @rows
SELECT col1, col2 FROM myTable
SET ROWCOUNT 0 --will work

If you use ROWCOUNT make sure to set it back to zero, this removes the limit.

Remember that SQL Server does not rely on internal storage order when processing queries, so the only way to make sure you get the right rows is to use an ORDER BY clause in your SELECT statement.

There is more detail on these statements in Books Online.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources