On Tue, May 31, 2011 at 11:23 AM, Lou Syracuse <[email protected]> wrote:
> As you have found SQL Server has no concept of record numbers...
>
> What you could do is add an integer column to that SQL Table (assuming you
> have rights to do so), and under the settings for that column expand
> "Identity Specification" and set (is Identity) to yes. This will set the
> column up much the way VFP does with its Auto-incrementing integer.
>
> Then reference that field with your randomly-generated number using the
> max() of the new field which You'll have to make a call to SQL to obtain
> first. Be sure to make sure you have a record returned when you make the
> call, if records are deleted from that table the ID's are not recycled so
> you could end up doing a query against a record that has been deleted and
> therefore not get a record back.
>
------------------
Sorry but TSQL has had row numbers for the past 3 years.
USE AdventureWorks2008R2;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
FROM Sales.SalesOrderHeader
)
-- Now fetch rows 50 -> 60
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;
The over() clause allows you designate how to apply recno to the set.
The problem requested was a Random audit so having a RowNumber is
still bizarre in that this is a disconnected environment and you may
have a table with 25 million rows. The way you index your table has
everything to do with how the BASE output from SQL Server is going to
come to you. Always has and always will.
Why not create an array or a temp table to hold the keys from N random IDs
SELECT TOP 1 ID
FROM TheTableIneedToAudit
Where createDate between @FirstofLastMonth and @LastofLastMonth
ORDER BY NEWID()
--
Stephen Russell
Unified Health Services
60 Germantown Court
Suite 220
Cordova, TN 38018
Telephone: 888.510.2667
901.246-0159 cell
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
