NEWID question. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

NEWID question.

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-05-28 : 03:15:42

hi.


SELECT TOP 1 cst_id 
FROM CONTEST_PARTICIPANTS 
INNER JOIN TRANSACTIONS ON TRANSACTIONS.trs_id = CONTEST_PARTICIPANTS.cst_trs_id 
WHERE contest_id = 1 AND
 cst_enter_date BETWEEN '1/1/1900' AND '1/1/2050' AND (TRANSACTIONS.trs_completed = 'True')
ORDER BY NEWID()

so this is suppose to pick a winner but i am not sure how the newid will select the correct range. I mean does newid only select results found in the query or it may go out of scope?
Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-05-28 : 03:59:56

First the query will have a result set which comes out of the WHERE clause.
Then "ORDER BY NEWID()" will shake the result set ie. random ordering.
Then TOP 1 takes the first row from the random ordered result set.

So you will get a random result/row.

Too old to Rock'n'Roll too young to die.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-05-28 : 04:19:25

"Then "ORDER BY NEWID()" will shake the result set ie. random ordering."
Yes that is what i can't grasp. NEWID() is not a column name, but a random number. How can this work?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-05-28 : 04:35:51

sometimes we just have to believe what we see :D

Too old to Rock'n'Roll too young to die.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-05-28 : 04:48:48

I'll just put it down as an sql "trick" .
Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-28 : 11:44:32

quote:
Originally posted by sapator

I'll just put it down as an sql "trick" .
Thanks.

Funny how anything we don't understand seems like magic

run this statement several times:

select number
       ,newid() as randomizer 
from   master..spt_values
where  type = 'P'
and    number between 1 and 10
order by 2

In this case the "trick" is to just not include NEWID() in the SELECT but only in the ORDER BY.

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-05-28 : 17:01:29

You need to brush up your skills on "Logical Query Processing Order".
See http://tsql.solidq.com/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-05-28 : 17:34:44

TG, but in your example we a column with newid() so it would also work like:
select number
,newid() as randomizer
from master..spt_values
where type = 'P'
and number between 1 and 10
order by NEWID()

So, ok newid() a random num but if i do this:
select number

from master..spt_values
where type = 'P'
and number between 1 and 10
order by 'F3CFC7E4-5F6A-40B2-AFBC-B861B0D053C3'

will say that a constant expression encountered so it requires columns so newid is a "magic" column :P
So anyhow i don't get it and SwePeso decided to make things worst with this diagram :)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-29 : 00:01:28

>>SwePeso decided to make things worst with this diagram :)
Ha - that's pretty funny. save off that link (as I did). It will come in handy someday if you keep up with this sql stuff.

>>Yes that is what i can't grasp. NEWID() is not a column name, but a random number. How can this work?
don't think of newid() as a random number. Think of it as an expression. The ORDER BY clause can contain any column that is represented by your FROM clause and expressions (that usually use one or more columns represented by the FROM clause but not necessarily). Of course constants make no sense in the ORDER BY clause.

The point behind the incomprehensible diagram is that only after the result rows are established can the sort ORDER be applied to them. In our example we have 10 rows to sort. We can sort by any column in spt_values and/or most any expression we want. An expression would resolve once per row so we can only have 10 expression values to sort. Because we chose newid() as the expression we get 10 random values every time we run the statement. By sorting on that expression we get a different sort order for those exact 10 rows every time.

If you still don't get it don't worry about it. You just need more experience stumbling through challenges. I've got 20 years of stumbling under my belt.

Be One with the Optimizer
TG

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-05-29 : 05:02:29

So it is like saying (a very clumsy example) that by ordering with an expression, we create a "hidden" Order columns that will have a different NEWID() for each row, and we sort by that "hidden" column(?).

P.S. I'm developing for about 8 years but i had jobs that "feed" me the SQL and others that i had to write it myself but i guess i am more in asp.net - vb.net programming and JS - CCS - HTML so i get this kinda silly questions for SQL sometimes. IT's like if i don't use it much i tend to forget. My previous project used extensive sprocs (roughly 100 sp's) and were medium complicated but now when i go to the t-sql code i wrote i sometimes cannot understand it :P

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-29 : 11:17:26

Yep - that's basically it. "hidden" in the sense that it is not in SELECT so not in the results.
Yeah stuff that I rarely do and have to re-figure out each time is like that for me. Basically anything that I can't do without a tech reference guide handy.

Be One with the Optimizer
TG

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-05-29 : 17:39:00

Ok, thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-05-30 : 08:45:29

NEWID() function is different from GETDATE() function.
While GETDATE() is statement-scoped (only evaluated once per statement), NEWID() function is evaluated once per row.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-30 : 10:19:43

quote:
Originally posted by SwePeso

NEWID() function is different from GETDATE() function.
While GETDATE() is statement-scoped (only evaluated once per statement), NEWID() function is evaluated once per row.
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

I would tweak your statement to say "once per function instance" because if you: SELECT newid() as guid1, newid() as guid2 you'll get different values across the row. And I haven't tested but if you also include ORDER BY newid() I suspect the order will be by yet different values.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources