a.* is asking for all fields, so you'd still reference them by field names, but I'd explicitly define which fields I wanted, such as
SELECT TOP 1 a.FieldName1, a.FieldName2, etc... Chris Tifer ----- Original Message ----- From: <[EMAIL PROTECTED]> To: "ActiveServerPages" <[EMAIL PROTECTED]> Sent: Wednesday, October 02, 2002 9:03 AM Subject: RE: Random picture from a Database > I can't believe I have to ask this, but how do I get the results of this > query? It's not like I'm asking for a Field ? > > > Tim > > -----Original Message----- > From: Bostrup, Tore [mailto:[EMAIL PROTECTED]] > Sent: October 1, 2002 5:51 PM > To: ActiveServerPages > Subject: RE: Random picture from a Database > > CONVERT is a SQL Server data type conversion function. > > Form MS Access/Jet, use CLng() instead. > > You should modify the statement a little in order to stay within the > boundaries... > > sSQL="SELECT TOP 1 a.* FROM Pictures a WHERE a.PIC_ID <= (SELECT > MIN(b.PIC_ID) + CLng(ROUND(RAND() * (MAX(b.PIC_ID) - MIN(b.PIC_ID)), 0))" > sSQL= sSQL & " FROM Pictures b) ORDER BY a.PIC_ID DESC" > > HTH, > Tore. > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, October 01, 2002 3:32 PM > To: ActiveServerPages > Subject: RE: Random picture from a Database > > > David, I tried to use your code and came up with this, though it might be > very buggy since I've never seen this type of thing before.... > > sSQL="SELECT TOP 1 a.* FROM Pictures a WHERE a.PIC_ID <= (SELECT > MIN(b.PIC_ID) + CONVERT(int, ROUND(RAND() * MAX(b.PIC_ID), 0))" > sSQL= sSQL & " FROM Pictures b) ORDER BY a.PIC_ID DESC" > > > I get the below error.............(also, my Intro.inc file is only 20 lines > long, so I have no idea where "Line 397" comes from) > > > > Microsoft JET Database Engine (0x80040E14) > Undefined function 'CONVERT' in expression. > /provinces/bc_yukon/intro.inc, line 397 > > Tim > > > -----Original Message----- > From: David L. Penton [mailto:[EMAIL PROTECTED]] > Sent: September 26, 2002 9:32 AM > To: ActiveServerPages > Subject: RE: Random picture from a Database > > 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.unsub%% > > --- > You are currently subscribed to activeserverpages as: [EMAIL PROTECTED] > To unsubscribe send a blank email to > %%email.unsub%% > > --- > You are currently subscribed to activeserverpages as: [EMAIL PROTECTED] > To unsubscribe send a blank email to > %%email.unsub%% > > --- > You are currently subscribed to activeserverpages as: [EMAIL PROTECTED] > To unsubscribe send a blank email to %%email.unsub%% --- You are currently subscribed to activeserverpages as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED]
