Get one row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lou - 29 May 2008 22:05 GMT

How do I, using an SQL statement only, get one row from a recordset.
I need to get at random rows one at a time.
Example: SELECT rownum 5 FROM table

-Lou

Reply to this Message

William Vaughn [MVP] - 29 May 2008 22:21 GMT

In a relational database the order in which rows is returned is arbitrary
and should not be built into the application logic. However, if you have a
primary key or an ordinary key that you wish to retrieve--even if it's an
arbitrary value, use a WHERE clause to reference the value as in:
SELECT myCol FROM Table WHERE myCol = 9

If you only want one row then either use the TOP 1 expression or use a
unique key--one that's not repeated in the table.

Signature

__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

> How do I, using an SQL statement only, get one row from a recordset.
> I need to get at random rows one at a time.
> Example: SELECT rownum 5 FROM table
>
> -Lou

Reply to this Message

Lance Wynn - 02 Jun 2008 02:42 GMT

I have used something like this before, and it seems to work fine:

Select top 20 * from [sometable] order by newid()

Then just loop through the recordset, and you have 20 random rows. adjust
the top number, to get more or fewer rows.

Signature

Support Fairtax Legislation
www.fairtax.org

"A government big enough to give you everything you want, is strong enough
to take everything you have."
-Thomas Jefferson

> How do I, using an SQL statement only, get one row from a recordset.
> I need to get at random rows one at a time.
> Example: SELECT rownum 5 FROM table
>
> -Lou

Reply to this Message

VB Forum / Database Access / June 2008

Get one row