June 13, 2012 at 11:46 am
Hi all,
I have table that contains lottery data :
CREATE TABLE dbo.tblLottery
(
customerId INT NOT NULL
IDENTITY(1, 1) ,
customerName NVARCHAR(50) NOT NULL ,
score INT NOT NULL ,
CONSTRAINT pk_tblLottery PRIMARY KEY CLUSTERED ( customerId ASC )
)
I want to select 5 winners randomly according to score for example a customer who has more score has greater chance to win.
June 13, 2012 at 11:58 am
Using SELECT TOP ....ORDER BY NEWID() is probably the most popular way to get randomized results
SELECT TOP 5 *
FROM dbo.tblLottery
ORDER BY NEWID()
Lowell
June 13, 2012 at 4:50 pm
farax_x (6/13/2012)
Hi all,I have table that contains lottery data :
CREATE TABLE dbo.tblLottery(
customerId INT NOT NULL
IDENTITY(1, 1) ,
customerName NVARCHAR(50) NOT NULL ,
score INT NOT NULL ,
CONSTRAINT pk_tblLottery PRIMARY KEY CLUSTERED ( customerId ASC )
)
I want to select 5 winners randomly according to score for example a customer who has more score has greater chance to win.
What is the range of Score?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2012 at 12:29 am
Something like this ?
Untested
with ctenum
as
(
Select top(100) Row_number() over (order by (select null)) as rown
from sys.columns
)
SELECT TOP 5 *
FROM dbo.tblLottery
join ctenum
on rown<=score
ORDER BY NEWID()
Of course you may get the same person back twice but thats the lottery for you 🙂
June 14, 2012 at 4:33 am
Tnx for your replies
Dave Ballantyne (6/14/2012)
Something like this ?Untested
with ctenumas
(
Select top(100) Row_number() over (order by (select null)) as rown
from sys.columns
)
SELECT TOP 5 *
FROM dbo.tblLottery
join ctenum
on rown<=score
ORDER BY NEWID()
Of course you may get the same person back twice but thats the lottery for you 🙂
How can I have distinct winners ?!
Jeff Moden (6/13/2012)
What is the range of Score?
Its between 1 and 1000
Lowell (6/13/2012)
Using SELECT TOP ....ORDER BY NEWID() is probably the most popular way to get randomized results
SELECT TOP 5 *
FROM dbo.tblLottery
ORDER BY NEWID()
Lowell you dont pay attention to score !
June 14, 2012 at 5:44 am
farax_x (6/13/2012)
...a customer who has more score has greater chance to win.
you'd have to explain the rules. for example, if i have a "score" of 5, do i have 5 total chances to win, and a person who has a score of 1 or NULL has just one chance?
i can't offer suggestions to attend to the score without the logical rule you want to use.
Lowell
June 14, 2012 at 6:59 am
Lowell (6/14/2012)
farax_x (6/13/2012)
...a customer who has more score has greater chance to win.you'd have to explain the rules. for example, if i have a "score" of 5, do i have 5 total chances to win, and a person who has a score of 1 or NULL has just one chance?
i can't offer suggestions to attend to the score without the logical rule you want to use.
Tnx for your attention,
score is not allow null column and as I said, score value is between 1 and 1000 and this field calculated in website according to some factors!
A person who has more score has much more chance to win than lower score one. Your example is totally true so as you said if you have a "score" of 5, you have 5 total chances to win, and a person who has a score of 1 has just one chance.
June 14, 2012 at 7:20 am
farax_x (6/14/2012)
Tnx for your repliesDave Ballantyne (6/14/2012)
Something like this ?Untested
with ctenumas
(
Select top(100) Row_number() over (order by (select null)) as rown
from sys.columns
)
SELECT TOP 5 *
FROM dbo.tblLottery
join ctenum
on rown<=score
ORDER BY NEWID()
Of course you may get the same person back twice but thats the lottery for you 🙂
How can I have distinct winners ?!
This can be achieved by using DENSE_RANK & ROW_NUMBER, have a play and post your SQL back if you cant get a solution working.
June 21, 2012 at 11:35 pm
Here's a tested solution including the test harness to validate that it seemed to be producing random results.
CREATE TABLE #tblLottery
(
customerId INT NOT NULL
IDENTITY(1, 1) ,
customerName NVARCHAR(50) NOT NULL ,
score INT NOT NULL ,
CONSTRAINT pk_tblLottery PRIMARY KEY CLUSTERED ( customerId ASC )
)
;WITH Tally (n) AS (
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns t1 CROSS JOIN sys.all_columns t2)
INSERT INTO #tblLottery
SELECT RIGHT('0000' + CAST(n AS VARCHAR), 5), ABS(CHECKSUM(NEWID())) % 1000
FROM Tally
--SELECT * FROM #tblLottery
GO
DECLARE @Lottery TABLE (customerID INT, customerName NVARCHAR(50), score INT)
SET NOCOUNT ON
SET STATISTICS TIME ON
SET STATISTICS IO ON
;WITH Tally (n) AS (
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns),
ApplyScore AS (
SELECT CustomerID, CustomerName, score
FROM #tblLottery
CROSS APPLY (SELECT n FROM Tally WHERE n BETWEEN 1 AND score) x)
INSERT INTO @Lottery
SELECT TOP 500 CustomerID, CustomerName, score
FROM ApplyScore
ORDER BY NEWID()
;WITH Customers AS (
SELECT CustomerID, CustomerName, score
,n=ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY (SELECT NULL))
FROM @Lottery)
SELECT TOP 5 CustomerID, CustomerName, score
FROM Customers
WHERE n=1
ORDER BY NEWID()
GO 10
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
DROP TABLE #tblLottery
Note that it is not particularly speedy.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 22, 2012 at 9:45 am
dwain.c (6/21/2012)
Here's a tested solution including the test harness to validate that it seemed to be producing random results.
CREATE TABLE #tblLottery
(
customerId INT NOT NULL
IDENTITY(1, 1) ,
customerName NVARCHAR(50) NOT NULL ,
score INT NOT NULL ,
CONSTRAINT pk_tblLottery PRIMARY KEY CLUSTERED ( customerId ASC )
)
;WITH Tally (n) AS (
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns t1 CROSS JOIN sys.all_columns t2)
INSERT INTO #tblLottery
SELECT RIGHT('0000' + CAST(n AS VARCHAR), 5), ABS(CHECKSUM(NEWID())) % 1000
FROM Tally
--SELECT * FROM #tblLottery
GO
DECLARE @Lottery TABLE (customerID INT, customerName NVARCHAR(50), score INT)
SET NOCOUNT ON
SET STATISTICS TIME ON
SET STATISTICS IO ON
;WITH Tally (n) AS (
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns),
ApplyScore AS (
SELECT CustomerID, CustomerName, score
FROM #tblLottery
CROSS APPLY (SELECT n FROM Tally WHERE n BETWEEN 1 AND score) x)
INSERT INTO @Lottery
SELECT TOP 500 CustomerID, CustomerName, score
FROM ApplyScore
ORDER BY NEWID()
;WITH Customers AS (
SELECT CustomerID, CustomerName, score
,n=ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY (SELECT NULL))
FROM @Lottery)
SELECT TOP 5 CustomerID, CustomerName, score
FROM Customers
WHERE n=1
ORDER BY NEWID()
GO 10
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
DROP TABLE #tblLottery
Note that it is not particularly speedy.
Great! tnx
July 24, 2022 at 3:55 pm
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply