Solved

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

Posted on 2013-06-04
19
429 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
using BEGIN END with IF statement.. 12 35
Retention Policy for Backups 1 13
Sql query for filter 12 21
BULK LOGGED - log full 9 12
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

707 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

14 Experts available now in Live!

Get 1:1 Help Now