?
Solved

SQL - How to do a random order/sort of table date

Posted on 2013-06-04
19
Medium Priority
?
444 Views
Last Modified: 2013-06-05
I have a table which contains the following columns:
NAME (Char, 36)
ADDRESS (Char, 36)
CITY (CHAR, 19)
ST (CHAR, 2)
ZIP (CHAR, 5)
ZIP4 (CHAR, 4)
DOB (CHAR, 8)
SEX (CHAR, 1)
SID (CHAR, 13)

The table is currently in NAME order.  I would like to know how I can put the rows in  a random order (i.e. JONES, SMITH, BAXTER, etc) with a SQL statement.  Possibly using a character from the SID (which is the state license number, alpha and digits.)  I COULD export to a text file, do this and reload, but I am not sure how to get the result table to end up in this order.

Any help that you can offer would be greatly appreciated!!
Thanks
0
Comment
Question by:mbroad02
  • 10
  • 7
  • 2
19 Comments
 

Author Comment

by:mbroad02
ID: 39219992
WHOOPS--title should say "SQL - How to do a random order/sort of table data"   NOT DATE!!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39220009
select * from yourtable
order by hashbytes('MD5',name)


the hashes aren't actually random but they won't be in an easily predictable order by name

you could use other hashing algorithms too, the trick is simply to generate something that won't correspond to alphabetic ordering

you could also simply order by a random number

select * from yourtable
order by crypt_gen_random(10)


the 10 isn't important either, pick any number.

you could also include a second parameter for the seed so the results could be reproducible.
0
 

Author Comment

by:mbroad02
ID: 39220015
I literally use the "order by hashbytes('MD5',name)"  ???

THis is good but will only produce a query result.  My goal is to have the TABLE in this random order...  :)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 74

Expert Comment

by:sdstuber
ID: 39220023
create a temp table, copy the data using the select above
(yes, use "order by hashbytes('MD5',name)"  assuming your column name is really name)

delete your original table
insert the randomly ordered data from the temp table

drop the temp table


however,  SQL is set based meaning the order of the data in the table is irrelevant, it's "effectively" randomized already.  Not really, it does have some order but not one you should count on.

You could sort the data alphabetically and insert to the table and there is no guarantee that it will be returned in sorted order.
0
 

Author Comment

by:mbroad02
ID: 39220032
Thanks.  I am aware that a table is not really 'sorted.'   Maybe I am thinking too hard...I already have a query (attached) which selects every 3rd row for a selection process.  Could I modify this query to start out with a "random order" table???
Thanks
0
 

Author Comment

by:mbroad02
ID: 39220046
Whoops--here is the query
Select-every-3rd.doc
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39220212
select * from
(select  s.*, row_number() over(order by hashbytes('MD5',s.sid)) as rn
from SOS_TABLE as s) as x
where rn % 3 = 0
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39220446
Typically SQL people use:

ORDER BY NEWID()

to get a random sort order.  [Hashes aren't random, as noted previously.]

Then, as before, to put that random order into an nth row setup, something like this:

SELECT
    NAME, ADDRESS, ..., SID,
    ROW_NUMBER() OVER(ORDER BY NEWID()) AS row_num
FROM dbo.tablename
0
 

Author Comment

by:mbroad02
ID: 39221781
I believe we are close.  Attached is the original query, updated with the "order by NEWID" syntax.  Unfortunately I get a syntax error for that line.

Any thoughts?

Thanks
random-query-1.doc
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39221875
no, you're still doing the temp table thing.  That was only suggested for the secondary question of loading table in a particular random sequence.  Which is, as you already knew isn't reliable or useful step anyway.


just use the query like in http:#a39220212  using whatever randomizer you want
ScottPletcher suggested newid in http:#a39220446  and gave and example of using it

you could do the same thing with the the crypt_gen_random too


select * from
(select  s.*, row_number() over(order by  crypt_gen_random(10)) as rn
from SOS_TABLE as s) as x
where rn % 4 = 0


also, please just post the code directly in the thread rather than hiding it in a word doc
0
 

Author Comment

by:mbroad02
ID: 39221967
As you may well be able to tell, I am not yet proficient in SQL, hence a little bit of confusion on my part.  Please excuse...

The queries you pointed to seem to be fine except one thing:

I want to take TABLE A---randomize the order---then select every 3rd row from this random order table to a NEW TABLE (TABLE B).  The queries I see only do a select but do not create or write to the final table.

Any thoughts?  I appreciate it.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39221995
select x.*   --- you'll probably want to list the columns explicitly so you don't pick up the "rn"
into YOUR_NEW_TABLE
from
(select  s.*, row_number() over(order by  crypt_gen_random(10)) as rn
from SOS_TABLE as s) as x
where rn % 3 = 0;
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39222052
ROW_NUMBER returns a column, so it needs to be part of the initial SELECT list (including having a comma before it to delimit it).

Try something like this:


SELECT derived.*
FROM (
    select A.*,
        ROW_NUMBER() OVER(ORDER BY NEWID()) AS row_num
    --into SOS_KEYNUMBER1
    from SOS_TABLE A
    --inner join SOS_TEMP B on A.SID = B.SID
) AS derived
where derived.row_num % 4 = 0
0
 

Author Comment

by:mbroad02
ID: 39222101
Hmmm...I try this:
select NAME,
       ADDRESS,
       CITY,
       STATE,
       ZIP,
       ZIP4,
       DATEOFBIRTH,
       SEX,
       SID  
into SOS_RANDOM_KEYNUMBER
from
(select  s.*, row_number() over(order by  crypt_gen_random(10)) as rn
from SOS_TABLE as s) as x
where rn % 3 = 0;


And I get this error:
Msg 195, Level 15, State 10, Line 12
'crypt_gen_random' is not a recognized built-in function name.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39222107
use one of the other randomizers
0
 

Author Comment

by:mbroad02
ID: 39222113
OK, but as I stated, I am not proficient.  I started this question to find out how to get the random order I desire so any specific detailed help would be greatly appreciated.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39222155
try one of these

select NAME,
       ADDRESS,
       CITY,
       STATE,
       ZIP,
       ZIP4,
       DATEOFBIRTH,
       SEX,
       SID  
into SOS_RANDOM_KEYNUMBER
from
(select  s.*, row_number() over(order by  newid()) as rn
from SOS_TABLE as s) as x
where rn % 3 = 0;


select NAME,
       ADDRESS,
       CITY,
       STATE,
       ZIP,
       ZIP4,
       DATEOFBIRTH,
       SEX,
       SID  
into SOS_RANDOM_KEYNUMBER
from
(select  s.*, row_number() over(order by  hashbytes('MD5',name)) as rn
from SOS_TABLE as s) as x
where rn % 3 = 0;
0
 

Author Comment

by:mbroad02
ID: 39222171
The hashbytes one worked great!! Thank you!!!
0
 

Author Closing Comment

by:mbroad02
ID: 39222173
Great help.  Thank you
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question