Solved

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

Posted on 2013-06-04
19
431 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 73

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
 
LVL 73

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 73

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 69

Expert Comment

by:ScottPletcher
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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
LVL 73

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 73

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 69

Expert Comment

by:ScottPletcher
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 73

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 73

Accepted Solution

by:
sdstuber earned 500 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

895 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now