Random records from 2 tables?

[rjoseph]

Hi Guys

I think I have an easy one for you.

I am currently using the following script to display records from 2 different tables (SQL server 2007) onto my page.

-------------script--------------
sSQL = "SELECT make, model, orderid, picturelink,reg year, engine, transmission, colour, mileage, price, county, vendor FROM TABLE1"
sSQL = sSQL & " UNION"
sSQL = sSQL & " SELECT makeofcar, requiredmodelof car, orderid ,orderid, regyear, vehiclecc, transmission, colour, requiredmiles, requiredcarpric e, yourcounty, terms FROM TABLE2"
sSQL = sSQL & " WHERE paymentReceived = 'X'"
sSQL = sSQL & " ORDER BY orderid"

--------------end--------------

However, I wish to display random records onto my page from both tables.

My database contains thounsands of records. How is this easily acheived in the most efficient way?

Any help would be fully appreciated

Best regards

Rod from the UK

[jhardman]

Rod, [code=asp]ORDER BY NEWID()[/code] (note, this is dependent on the type of db, this is how you would do it in MS SQL Server). Let me know if this works for you.

Jared

[rjoseph]

Hi Jarod

Thank you for your reply.

Unfortunatley, this doesn't work for me.

All I get is the following error:

--------------error-------------
Microsoft OLE DB Provider for SQL Server error '80040e14'

ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator
------------end-------------

My Script now reads:

------------revised script----------------
sSQL = "SELECT make, model, orderid, picturelink,reg year, engine, transmission, colour, mileage, price, county, vendor FROM ClientTable"
sSQL = sSQL & " UNION"
sSQL = sSQL & " SELECT makeofcar, requiredmodelof car, orderid ,orderid, regyear, vehiclecc, transmission, colour, requiredmiles, requiredcarpric e, yourcounty, terms FROM Contact"
sSQL = sSQL & " WHERE paymentReceived = 'X'"
sSQL = sSQL & " ORDER BY NEWID()"
------------revised script end-------------------

If you have any ideas then that would be great.

I look forward to hearing from you

Rod from the UK

[markrawlingson]

I believe the NEWID() method works with the autonumber / autoincrement field in your table. It doesn't look like you're specifying one in your query(ies). Try putting the autonumber field for each table into both of the select statements.

If that doesn't work then try SELECT * FROM and see if it works when you specify all columns.

I'm 90% sure you'll need an autonumber field for NEWID to work so make sure you have one in there somewhere

Sincerely,
Mark

[rjoseph]

Hi Mark

Thank you for your reply.

I don't think I have an autonumber or autoincrement field in my tables. What do I do now?

Any help would be fully appreciated

best regards

Rod from the UK

[jhardman]

Rod,

several other DBs have an ORDER BY RAND() (or RANDOM()) option, but I don't think SQL SERVER does. You might have to pull up the records, do a randomize function to get a random number between 1 and the record count, then use the rs.move() method to move to your randomly selected record. Does that suggest a good method?

Jared

[markrawlingson]

If you can't get the NEWID() function to work, I would try doing what Jared suggested, it should work very nicely

[jobspider1000]

Hi

The reasons and ways to avoid this error have discussed in this

site with good examples. By making small changes in the query

http://sqlerror104.blogspot.com/2009/08/order-by-items-must-appear-in-select_19.html