Newid() function in sql

Database
Enthusiast

Newid() function in sql

hi,

 

ineed help with convert Newid() function to TERA DATA:

select tt_AllBrandsPlayers.*,row_number() over(partition by Segment_ID,brand_id order by ABS (CAST(CAST(NEWID() AS VARBINARY) AS INT)))

 

Thanks!

6 REPLIES
Senior Apprentice

Re: Newid() function in sql

 
Senior Apprentice

Re: Newid() function in sql

This NEWID is only used to order randomly, unluckily RANDOM can't be used within an OLAP-function.

 

To get truely random order you must nest it in a Derived Table

select tt_AllBrandsPlayers.*,row_number() over(partition by Segment_ID,brand_id order by rnd)
from
 ( select ..., 
      random(1,10000) as rnd
   from ...
 ) as dt

A bit less random is 

row_number() over(partition by Segment_ID,brand_id rows unbounded preceding)
Enthusiast

Re: Newid() function in sql

tnx for your answer.

i try to apply it :

CREATE MULTISET VOLATILE TABLE tt_AllBrandsPlayers,NO LOG AS

( select dt.*,row_number() over(partition by Segment_ID,brand_id order by randNum)

from

( select brand_id,Segment_id,random(0.000001,10000000) as randNum

from tt_AllPlayers ) as dt)

WITH DATA ON COMMIT PRESERVE ROWS;

 

but i still have error: Column names must be specified when SELECT list contains an unnamed expression. 

also i need to use order be ASC- where u think i write it in the code?

 

thanks alot !!

Enthusiast

Re: Newid() function in sql


dnoeth wrote:

This NEWID is only used to order randomly, unluckily RANDOM can't be used within an OLAP-function.

 

To get truely random order you must nest it in a Derived Table

select tt_AllBrandsPlayers.*,row_number() over(partition by Segment_ID,brand_id order by rnd)
from
 ( select ..., 
      random(1,10000) as rnd
   from ...
 ) as dt

A bit less random is 

row_number() over(partition by Segment_ID,brand_id rows unbounded preceding)


dnoeth wrote:

This NEWID is only used to order randomly, unluckily RANDOM can't be used within an OLAP-function.

 

To get truely random order you must nest it in a Derived Table

select tt_AllBrandsPlayers.*,row_number() over(partition by Segment_ID,brand_id order by rnd)
from
 ( select ..., 
      random(1,10000) as rnd
   from ...
 ) as dt

A bit less random is 

row_number() over(partition by Segment_ID,brand_id rows unbounded preceding)


dnoeth wrote:

This NEWID is only used to order randomly, unluckily RANDOM can't be used within an OLAP-function.

 

To get truely random order you must nest it in a Derived Table

select tt_AllBrandsPlayers.*,row_number() over(partition by Segment_ID,brand_id order by rnd)
from
 ( select ..., 
      random(1,10000) as rnd
   from ...
 ) as dt

A bit less random is 

row_number() over(partition by Segment_ID,brand_id rows unbounded preceding)


dnoeth wrote:

This NEWID is only used to order randomly, unluckily RANDOM can't be used within an OLAP-function.

 

To get truely random order you must nest it in a Derived Table

select tt_AllBrandsPlayers.*,row_number() over(partition by Segment_ID,brand_id order by rnd)
from
 ( select ..., 
      random(1,10000) as rnd
   from ...
 ) as dt

A bit less random is 

row_number() over(partition by Segment_ID,brand_id rows unbounded preceding)


dnoeth wrote:

This NEWID is only used to order randomly, unluckily RANDOM can't be used within an OLAP-function.

 

To get truely random order you must nest it in a Derived Table

select tt_AllBrandsPlayers.*,row_number() over(partition by Segment_ID,brand_id order by rnd)
from
 ( select ..., 
      random(1,10000) as rnd
   from ...
 ) as dt

A bit less random is 

row_number() over(partition by Segment_ID,brand_id rows unbounded preceding)


dnoeth wrote:

This NEWID is only used to order randomly, unluckily RANDOM can't be used within an OLAP-function.

 

To get truely random order you must nest it in a Derived Table

select tt_AllBrandsPlayers.*,row_number() over(partition by Segment_ID,brand_id order by rnd)
from
 ( select ..., 
      random(1,10000) as rnd
   from ...
 ) as dt

A bit less random is 

row_number() over(partition by Segment_ID,brand_id rows unbounded preceding)


dnoeth wrote:

This NEWID is only used to order randomly, unluckily RANDOM can't be used within an OLAP-function.

 

To get truely random order you must nest it in a Derived Table

select tt_AllBrandsPlayers.*,row_number() over(partition by Segment_ID,brand_id order by rnd)
from
 ( select ..., 
      random(1,10000) as rnd
   from ...
 ) as dt

A bit less random is 

row_number() over(partition by Segment_ID,brand_id rows unbounded preceding)


dnoeth wrote:

This NEWID is only used to order randomly, unluckily RANDOM can't be used within an OLAP-function.

 

To get truely random order you must nest it in a Derived Table

select tt_AllBrandsPlayers.*,row_number() over(partition by Segment_ID,brand_id order by rnd)
from
 ( select ..., 
      random(1,10000) as rnd
   from ...
 ) as dt

A bit less random is 

row_number() over(partition by Segment_ID,brand_id rows unbounded preceding)


dnoeth wrote:

This NEWID is only used to order randomly, unluckily RANDOM can't be used within an OLAP-function.

 

To get truely random order you must nest it in a Derived Table

select tt_AllBrandsPlayers.*,row_number() over(partition by Segment_ID,brand_id order by rnd)
from
 ( select ..., 
      random(1,10000) as rnd
   from ...
 ) as dt

A bit less random is 

row_number() over(partition by Segment_ID,brand_id rows unbounded preceding)


dnoeth wrote:

This NEWID is only used to order randomly, unluckily RANDOM can't be used within an OLAP-function.

 

To get truely random order you must nest it in a Derived Table

select tt_AllBrandsPlayers.*,row_number() over(partition by Segment_ID,brand_id order by rnd)
from
 ( select ..., 
      random(1,10000) as rnd
   from ...
 ) as dt

A bit less random is 

row_number() over(partition by Segment_ID,brand_id rows unbounded preceding)


dnoeth wrote:

This NEWID is only used to order randomly, unluckily RANDOM can't be used within an OLAP-function.

 

To get truely random order you must nest it in a Derived Table

select tt_AllBrandsPlayers.*,row_number() over(partition by Segment_ID,brand_id order by rnd)
from
 ( select ..., 
      random(1,10000) as rnd
   from ...
 ) as dt

A bit less random is 

row_number() over(partition by Segment_ID,brand_id rows unbounded preceding)












tnx for your answer.

i try to apply it :

CREATE MULTISET VOLATILE TABLE tt_AllBrandsPlayers,NO LOG AS

( select dt.*,row_number() over(partition by Segment_ID,brand_id order by randNum)

from

( select brand_id,Segment_id,random(0.000001,10000000) as randNum

from tt_AllPlayers ) as dt)

WITH DATA ON COMMIT PRESERVE ROWS;

 

but i still have error: Column names must be specified when SELECT list contains an unnamed expression. 

also i need to use order be ASC- where u think i write it in the code?

 

thanks alot !!

Senior Apprentice

Re: Newid() function in sql

Of course you need to alias the row_number.

 

"also i need to use order be ASC"

What order? You assign the row_number randomly.

Teradata Employee

Re: Newid() function in sql

You are creating a table, and as @dnoeth says, the result of the row_number() function must be given a column name!

Also, since you are creating a table, no matter which row you think is more important, data sequence has no meaning within a table, so order-by would not be accepted in the select statement.  A table is not a flat file.