[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


sql random field infomation from unrealted table

Posted on 2014-08-16
Medium Priority
Last Modified: 2014-08-17
I have a table of image records in an MS SQL table TBLimages with a field imageFilename with 1,000 records.

I have another separate table of listing possible Fonts with no relationship to the image file with 100 records with a key field of FontId

I want to create an SQL statement that will provide only all the records from TBLimages but with an extra column for a random valid value of FontId from the Font table

But I don't want extra records created and cannot use a group by as too many fields in TBLimages .

Help would be appreciated.
Question by:stephenwilde
  • 5
  • 4
LVL 14

Expert Comment

by:Russell Fox
ID: 40265195
You can use ORDER BY NEWID() to generate random data. Here the TOP 1 clause is pulling a random font because the font table is being ordered by a randomly-generated GUID:
SELECT Field1,
FROM TBLimages

Open in new window


Author Comment

ID: 40265286
Thank you,  if you run the SQL several times each time it comes back with a different random FontId

But the problem is the same FontId is against all the records

Looking for a different random FontId for each record in the SQL result.
LVL 30

Expert Comment

by:Olaf Doschke
ID: 40265340
I wonder, why you think a group by depends on the number of fields. Grouping is not wanted here at all, is it?

If you don't care whether each font is used at least once you can expand on the idea of Russel and create a random font id by a random number you generate by ABS(CHECKSUM(NewId())) % 100+1 for numbers between 1 and 100.

Random numbers and why you can't simply use RAND() explained in detail: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-set-based-random-numbers/

In your case if Font IDs are from 1 to 100 you don't even need to join the font table, simply do

SELECT *, ABS(CHECKSUM(NewId()))%100 + 1 as FontID FROM TBLimages

Open in new window

More general you could determine Max fontid first and put it into the query instead of 100:

DECLARE @MaxFontID AS int;

SELECT @MaxFontID = MAX(FontID) FROM TBLfonts;

SELECT *, ABS(CHECKSUM(NewId()))%@MaxFontID + 1 as FontID FROM TBLimages;

Open in new window

Bye, Olaf.
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks


Author Comment

ID: 40265379
Thanks for replies.

I didn't want to make the problem to specific originally, so didn't go in this detail.

But the font table doesn't exactly for 100 records and it has a flag bool field of isLive, so not all font id's are suitable,
so a random number between 1 and max number numbers will not work here, but thanks for the suggestion.
LVL 30

Expert Comment

by:Olaf Doschke
ID: 40265431
Then a bit more complex

DECLARE @Reccount AS int;

SELECT @Reccount = Count(*) FROM TBLfonts Where isLive=1;

;With RNDFonts AS
(SELECT ROW_NUMBER() OVER(ORDER BY FontID) as RowNum, FontID FROM TLBfonts Where isLive=1)

SELECT TBLimages.*, RNDFonts.FontID FROM TBLimages
INNER JOIN RNDFonts On RowNum = ABS(CHECKSUM(NewId()))%@Reccount + 1

Open in new window

Bye, Olaf.

Author Comment

ID: 40265465
Thanks very inventive using the row number.

But when run on the data although the Select;
(SELECT ROW_NUMBER() OVER(ORDER BY FontID) as RowNum, FontID FROM TLBfonts Where isLive=1)
produces records
when added with inner join on the last Select : NO records are produced at all.
LVL 30

Expert Comment

by:Olaf Doschke
ID: 40265507
I see - if you try with just a few records you get some results, but seldom all images. The problem is the way SQL Server evaluates the join condition, it doesn't create one random value per image and finds the RowNum in RNDFonts, it scans RNDFonts and you have to be very lucky the random value matches RowNum.

So we have to do it with 2 CTEs:

Declare @Reccount AS int;

SELECT @Reccount = Count(*) FROM TBLfonts Where isLive=1

;With RNDfonts AS
, RNDimages AS
(SELECT ABS(CHECKSUM(NewId()))%@Reccount + 1 as RandomRowNum, ImageID FROM TBLimages)

SELECT ImageID, FontID FROM RNDimages LEFT JOIN RNDFonts On  RandomRowNum = RowNum

Open in new window

Edit: For some reason it has to be a LEFT join in the final query, not an INNER join. But changing just that in the old solution does not solve it, the whole thing only works with the RNDimages CTE.

Bye, Olaf.

Author Comment

ID: 40265974
Thank you the solution you suggested did indeed product random valid Font ID in TBLimages.

Your solution is something well beyond my ability and it does work.

However all the FontID's are all in growing sequence  with higher numbers in TBLimages.

so random numbers generated;
record 1 fontid = 4
record 2 fontid =10
record 3 fontid=12

Prefer a solution that generates a truly random valid FontID for each record.
LVL 30

Accepted Solution

Olaf Doschke earned 2000 total points
ID: 40266004

I get random results in fontids with this sqlfiddle.

It creates a random valid fontid (of a font that isLive = 1) for every imageid, not rising sequence fontids.

Bye, Olaf.

Author Closing Comment

ID: 40266064
Thanks for persisting with the solution,
 I had an extra order by on my SQL code once this was adjusted for it was a random FontID.

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

834 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