Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-06-04
19
Medium Priority
?
439 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

609 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