SELECT TOP 1
a.*
FROM
MyTable a
WHERE
a.id <= (
SELECT
MIN(b.id) + CONVERT(int, ROUND(RAND() * MAX(b.id), 0))
FROM
MyTable b
)
ORDER BY
a.ID DESC
is the other query [that should be converted to your particular platform -
Access in this case) but of course the SQL Server solution is good (well,
for SQL Server :-)
SELECT * --put column names here!!!
FROM tbl
ORDER BY NEWID()
David L. Penton, Microsoft MVP
JCPenney Application Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the
Soul. - J.S. Bach"
[EMAIL PROTECTED]
Do you have the VBScript Docs or SQL BOL installed? If not, why not?
VBScript Docs: http://www.davidpenton.com/vbscript
SQL BOL: http://www.davidpenton.com/sqlbol
-----Original Message-----
From: Andy Hayman [mailto:[EMAIL PROTECTED]]
Odd I am using a DSN to connect to an Access DB using:
strSQL = "SELECT TOP 1 * FROM Comments ORDER BY NEWID()"
I am getting
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'NEWID'
in expression.
Andy
> -----Original Message-----
> From: Andrew Zetterman [mailto:[EMAIL PROTECTED]]
>
> SQL Books Online
> -----------------------------
>
> NEWID
> Creates a unique value of type uniqueidentifier.
>
> Syntax
> NEWID ( )
>
> Return Types
> uniqueidentifier
> -----------------------------
>
> So if you put it into your order by it will generate a unique
> id for each record and then it will sort by that column.
> Each time you query the unique id will be different so the
> order of your records will change. So the top 1 record will
> randomly change.
>
> Andrew
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
>
> How exactly does the below code work?, I haven't seen that before.
>
> -----Original Message-----
> From: Andrew Zetterman [mailto:[EMAIL PROTECTED]]
>
> David Penton posted a query a few weeks ago that could select
> a random record.
>
> SELECT TOP 1 col FROM tbl ORDER BY NEWID()
>
> HTH,
>
> Andrew
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
>
> But how do I get a random pic from the remaining pics?
>
> -----Original Message-----
> From: Matthew Small [mailto:[EMAIL PROTECTED]]
>
> Put a binary field in your table. If the field is set, don't
> use that picture.
>
> Matthew Small
> IT Supervisor
> Showstopper National Dance Competitions
> 3660 Old Kings Hwy
> Murrells Inlet, SC 29576
> 843-357-1847
> http://www.showstopperonline.com
>
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
>
> I just want to show a random picture form all those in my
> database. I used to just get the number of pics in my DB,
> then geta RND # and then call that ID, but, because I
> Autonumber them, when a pic got deleted, I would sometimes
> get an error.
>
> So, how can I get a rnd Pic ID from a database that has say
> 20 pics, but 10 are set to NOT be shown....ideas?
>
> Tim
---
You are currently subscribed to activeserverpages as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
