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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-28 : 08:08:35
|
| Todd writes "HiHow do I select one field out of a database following the guidlines ??Heres my select statment.strSQL = "SELECT tblUsers.url FROM tblUsers WHERE tblUsers.Pops >= 5 AND tblUsers.ID <>= '" + strID + "' AND rowcount <=1 ORDER BY tblUsers.Pops DESC"the bit that says rowcount <=1 is supposed to only give me one result out of the many its returns, rowcount is Oracle how does it work in MS SQL." |
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-03-28 : 08:11:59
|
| As far as I know there is not a rowcount function in SQL. However, you should be able to do a TOP N at the beginning of your SELECT statement to return the number of rows you want.SELECT TOP 1 tblUsers.url FROM tblUsers WHERE tblUsers.Pops >= 5 AND tblUsers.ID <>= '" + strID + "' ORDER BY tblUsers.Pops DESCJeremy |
 |
|
|
davidpardoe
Constraint Violating Yak Guru
324 Posts |
Posted - 2002-03-28 : 08:38:15
|
Yes there is  set ROWCOUNT 5will limit the number of rows returned by any subsequent query to 5.============================Chairman of The NULL Appreciation Society"Keep NULLs as NULL" |
|
|
|
Jay99
468 Posts |
Posted - 2002-03-28 : 09:44:53
|
quote:
...Tables have columns, not fields and there is a huge difference...-Celko 03/25/2002 15:42:28
Jay<O> |
|
|
|
aclarke
Posting Yak Master
133 Posts |
Posted - 2002-03-29 : 00:34:10
|
quote:
set ROWCOUNT 5will limit the number of rows returned by any subsequent query to 5.
And as I learned after a while, you set it back by typing set rowcount 0, not set rowcount 999999999999999999999 and hoping that was big enough...  Of course, when I discovered select top n [percent], I never really used rowcount any more, anyways... |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-03-29 : 07:53:20
|
| Sorry guys. I completely forgot about SET ROWCOUNT. One thing to consider if you do use SET ROWCOUNT though is that as aclark pointed out, you must set the value back to 0 when finished as this affects all SQL statements run, where TOP N performs only against the current SQL statement.Does anyone know whether or not SET ROWCOUNT affects all SQL statements running on the server or only SQL statements run by the user who set the rowcount? Jeremy |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-29 : 08:39:56
|
| SET ROWCOUNT only affects the current session. A user could connect twice to the same server, and have two different ROWCOUNT settings.The nice thing about ROWCOUNT, as was mentioned, is that it affects all SQL statements. If you've got to UPDATE 1 million rows and your log keeps filling up, or it takes a long time for the checkpoint/commit, you can SET ROWCOUNT 100000 and run it 10 times. Might sound dumb, but I've had UPDATEs that took 2 hours to do as one operation, that finished in 1/2 hour using a ROWCOUNT limit. |
|
|
|
|
|
|
|
|
Selecting one field