>actually thats not the same. SET ROWCOUNT will stop processing when >@MAXROWS is reached, it ignores ORDER BYs. a TOP will order first >then truncate the resultset.
are you sure about it ignoring ORDER BYs? I just ran a test with SQL Server 7.0 and it returned the correct results and I included an order by. here are the results select pk from table3 order by name DESC pk ------------------------------------ 040BDF21-CE4D-4FD6-9817-5DC3D668D7DD 9E7955EB-1E45-4009-911C-9506C5248293 5BE8F249-D16A-46F5-97B3-2808848748AE 0342D3EB-A354-4E67-939D-0675643C9F36 EACD64D0-8C56-4367-8BD3-D1D34CA6DEE6 E1B43558-23A1-4608-8069-A4FF8A478FD5 ED936953-93FA-4405-9BCE-BC35A095E72B 682C6DBF-64BD-44CF-80CB-4B6B5FF72A65 4B5C7470-D644-47DB-92B7-B300BEBA3F30 19B1B62B-96CA-4D2B-9367-576F9584EFC1 43FE78C0-CCD8-4E2A-870B-01155A2FC5F1 16CC9F15-962D-41C7-8AAD-07346B9BF05B 325090CD-2387-486D-B443-BDEBA050DEC3 0386D88A-4EE6-4E56-9252-CAB15C9C7657 9C56BCE3-80B7-46D6-9B50-3876C68C427D E7549832-EE78-4284-81E1-0C8D4A187326 A1C8D584-C0C3-49BF-B0DB-A9DED7AB76C9 29A6CA5F-C4DB-4405-8983-55FCD5378985 F6D183E2-F743-481E-B86B-5350FF66A10A 1F5783E4-4A92-4386-B2C5-54A0EBA1E8C3 0E0B559D-51AB-4C08-9DCD-7FB2BCB486F8 064AE841-45B3-432B-898F-D999B8FB4386 EE074E81-073C-4EBF-B42E-1B0A8DA3E244 F60B46B3-80E2-42C0-97D9-3A416CE6AEC3 A0921845-F4D3-4860-AFFD-A3FBB366A511 79F571C9-E8C1-42BD-ACBF-163E0578C831 472C6D79-4EA8-49F0-8BC8-B7F44510B892 606F2DCA-6EB8-4EFC-86BD-CECDA31E3B14 211EA2C1-7645-4767-AE8B-062B22789FDC FD784A0A-A10B-4CF4-A20E-0A33A9A0E4C5 CE11B251-08E6-4778-B4B7-21C6DD87BE07 C68D214C-4EDB-42AD-99B0-218EEBD746C3 B87AF4B4-4EFA-4523-8A95-6DAE21C791BC 15D968EC-1DE1-487B-992C-6DBA7F8FCF8F E1AFCD40-317E-41D6-BB60-5703A5938929 096F710F-71CA-4BBF-A14D-1BB6C80800DC E1D4DAFF-7DA9-449B-BF29-651694AA756E 49AF6EA8-C163-43C2-A91F-5ACD4F243EB5 DD102BFA-AB76-4057-8B5E-2563A3CA9F79 25FA065D-30A3-4A7D-AEEA-19FC0A3B0B89 C0B553D2-80F6-40ED-88E2-FF17BB566AC1 560989BD-B6E9-45FF-BA6C-351F38E11615 EF35EFE8-9475-40FE-AC6E-298AA9C07C52 D70C93CD-1B61-48CE-A0A7-E59D8CB0F36B 01FBCD3C-949E-47D5-8611-D8814FE2AC36 70CCA643-2291-4C29-8BF4-23E67A7AFB7E 3E1D788F-B4AB-4118-AE3A-B9C0B54B09F3 05664027-2E9F-44EC-9453-FDB6A74AADB8 0642A382-6352-4F02-8AB2-23077EF45AC4 58792375-6D8B-49A1-9D9B-2FCD2C543EE4 573FF8EF-524B-4398-995B-D9AEFB66F959 4D53FBBC-F9F6-41F8-AE95-E284C0722BFC 7AE32E5A-DEB1-4025-8723-8041B16D3553 4383EA6C-6DBD-4B87-A00D-E019A9E44257 A197B192-3347-404B-BDAD-02DB78A9818D 6EE26A6B-3D36-4FCC-A801-25B8640B5223 70F57CDF-69D6-4569-8FDA-BD5E76B2095D (57 row(s) affected) set rowcount 10 select pk from table3 order by name DESC set rowcount 0 pk ------------------------------------ 040BDF21-CE4D-4FD6-9817-5DC3D668D7DD 9E7955EB-1E45-4009-911C-9506C5248293 5BE8F249-D16A-46F5-97B3-2808848748AE 0342D3EB-A354-4E67-939D-0675643C9F36 EACD64D0-8C56-4367-8BD3-D1D34CA6DEE6 E1B43558-23A1-4608-8069-A4FF8A478FD5 ED936953-93FA-4405-9BCE-BC35A095E72B 682C6DBF-64BD-44CF-80CB-4B6B5FF72A65 4B5C7470-D644-47DB-92B7-B300BEBA3F30 19B1B62B-96CA-4D2B-9367-576F9584EFC1 (10 row(s) affected) Anthony Petruzzi Webmaster 954-321-4703 [EMAIL PROTECTED] http://www.sheriff.org -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 9:15 AM To: CF-Talk Subject: Re: SELECT TOP n vs MaxRows... > about TOP is that you can't set it dynamically, as is TOP @maxrows. To do > this, use the SET ROWCOUNT @maxrows. Just be sure that after your SQL > statement you do SET ROWCOUNT 0 to reset it. actually thats not the same. SET ROWCOUNT will stop processing when @MAXROWS is reached, it ignores ORDER BYs. a TOP will order first then truncate the resultset. ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
