Solved

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

Posted on 2013-06-04
19
432 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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: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 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: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 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server syntax question 13 32
SYbase 4 31
why sql server only update some statistics in the database ? 3 22
SQL Server - Set Field Values ito Zero Based on Related Table 4 26
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

777 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