Here's a fun one! :)
-
So, I need a query that will return the same rows of data in a completely random order every time it is executed. Any ideas? I must add to my list of frustrations with SQL Server that the Rand() function returns one value for the query, rather than per record. 😛
Thanks in advance for any help.
-
Whilst not being the answer that you want, could I suggest that you firstly tell us why you need this, and then perhaps my second comment could be more relevant, and that is to do this in client-side code? It could be that you need these results in a randomised order for use within a stored procedure. In that case, storing the results plus an additional column for a random number, cycling through that work table populating the number, then selecting ordered by the number would do what you want. However, perhaps this is an example where doing it in the client to avoid the server doing that looping exercise would be preferable? There is too much emphasis on this forum with people who *have* to have everything done on the server, come what may. The average desktop is more than capable of processing a result set into a form suitable for your needs, so why complicate it overly and load the server with this task? Sorry, just my Friday afternoon rant.
-
select col from table order by newid()
Regards,
Andy Jones
.
-
Bravo Andy!
Jay
Jay Madren -
aww come-on... Mr. SJC, you're no fun. I had intentionally left my post as open-ended as possible so as not to bias the responses I got. I certainly am not hinging a critical time-line on the responses I *Have* to get here. : ) As a matter of fact, the query will be used in a stored procedure to then run a function on every record, so I want to avoid having the data passed back and forth each time.
As I'm typing, I saw Andy's answer come in. Looks good! Anybody see any problem with that?
-
I knew I'd get shot down in flames!! Well it was worth a try. I thought there might be a comment about the result set crossing the network... I would say in my defence your honour that we were not told the reason why you needed this, which is always useful. Next time, I won't just jump in.
Regards
Simon
-
Simon, Cheer up old pal. It's Friday!
And hey, Jump-Away! Up until the time that Andy got involved, you were leading the pack with ideas. 😉 Like I said, I didn't want to bore you with the menaial details of my request. Although, if some bizzare twist of fate cause you ever have to step into my code I wouldn't blame you for asking.
-
I need more posts. I'm tired of this "Starting Member" moniker. I'd like to thank Andy for spoiling my "fun". I was hoping to get some huge flaming thread going here.
-
sorry...
Regards,
Andy Jones
.
-
Yes, perhaps I should have made my comments a bit more contentious to encourage a wider debate...come to think of it, what do other forum members think of my comment? Is it a mindset that people have got into that determines that absolutely everything has to take place on the database server? How does this reconcile with the use of a middleware layer which would be written in a language which is more adept at manipulating datasets than transact-SQL (that might encourage some comments as well)?
-
The great thing about my subject line is that we could talk about anything here!
I think Simon does have an intersting point. This is the first project my development team has done where they rely on Stored Procedures, rather than dynamic SQL written in their apps, to do the bulk of data retrieval and updates. One question we recently came across was how to indicate the progress of a lengthy stored procedure to a client app that was waiting for completion. Previously, the app would send one statement to the DB for each of the thousand records in its data set, knowing exactly where it was in the list. Now, the DB is doing the processing for the whole set at once, and that can take minutes. ?? -
Ok let's have a little fun then. What happens if the table is defined like so.
CREATE TABLE testC (
a char(2000) NOT NULL,
b char(2000) NOT NULL,
c char(2000) NOT NULL,
d char(2000) NOT NULL,
e char(39) NOT NULL
)
DECLARE @al-2 char(1)
set @al-2 = 'a'
INSERT testC (a, b, c, d, e) values (replicate(@al, 2000),replicate(@al, 2000),replicate(@al, 2000),replicate(@al, 2000),replicate(@al, 39))
set @al-2 = 'b'
INSERT testC (a, b, c, d, e) values (replicate(@al, 2000),replicate(@al, 2000),replicate(@al, 2000),replicate(@al, 2000),replicate(@al, 39))
set @al-2 = 'c'
INSERT testC (a, b, c, d, e) values (replicate(@al, 2000),replicate(@al, 2000),replicate(@al, 2000),replicate(@al, 2000),replicate(@al, 39))
set @al-2 = 'd'
INSERT testC (a, b, c, d, e) values (replicate(@al, 2000),replicate(@al, 2000),replicate(@al, 2000),replicate(@al, 2000),replicate(@al, 39))
you have several rows of data all columns filed to max size and you run this
select a, b, c, d, e from testC
which works but now you want to order the data.
using Order By NEWID()
however because the table width will create a table with a row greater than 8K and a temp table is autogenerated in tempDB to preform the order by you will get an error.
So what then?
This should be interesting on the response. Keep in mind I will throw in kinks as it goes.
-
Antares, you are a Sick DBA!!! I get the error (using your test case), but have no idea yet how to avoid it. I suppose you have something up your sleeve?
-
Now the guru has gone and put a spanner in the works...doesn't this relate to the debate here a few weeks ago concerning obtaining row numbers or row id's? If one of your columns was unique or a pk, then we could create another table, populate it with key data from yours, then generate the newid() on table2, then select back joining to the original?
Or some smart reared person will tell me I'm wrong...
-
select a, b, c, d, e
from testC ,(select cast(newId() as varchar(50)) as GUID) as B
Order By GUID
sorry...
I think Simon does have an intersting point. This is the first project my development team has done where they rely on Stored Procedures, rather than dynamic SQL written in their apps, to do the bulk of data retrieval and updates. One question we recently came across was how to indicate the progress of a lengthy stored procedure to a client app that was waiting for completion. Previously, the app would send one statement to the DB for each of the thousand records in its data set, knowing exactly where it was in the list. Now, the DB is doing the processing for the whole set at once, and that can take minutes. ??Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply