Select Random entries with criteria

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

General SQL Server Forums

New to SQL Server Programming

Select Random entries with criteria

Author

Topic

dimoss
Yak Posting Veteran

52 Posts

Posted - 2008-11-07 : 07:57:06

Hi,

I have a table with two columns 'id', 'tickets'.
I want to make a draw and select randomly id's where the total of the tickets is a given number

For example...

id tickets
-----------
1 3
2 5
3 1
4 2
5 1
6 2
7 5
8 1
9 4
10 2
----------

I would like to select randomly 5 id's where the total of the tickets would be 10.

Is it possible?

Thanks in advance

Denis

www.tabletennis.gr

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-07 : 08:01:34

Yes.
See http://weblogs.sqlteam.com/peterl/archive/2008/08/12/How-to-sum-up-an-unknown-number-of-records.aspx

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-07 : 08:12:45

Or this simpler but slower on a large table

DECLARE	@Sample TABLE
	(
		ID TINYINT NOT NULL,
		Tickets TINYINT NOT NULL
	)

INSERT	@Sample
SELECT	 1, 3 UNION ALL
SELECT	 2, 5 UNION ALL
SELECT	 3, 1 UNION ALL
SELECT	 4, 2 UNION ALL
SELECT	 5, 1 UNION ALL
SELECT	 6, 2 UNION ALL
SELECT	 7, 5 UNION ALL
SELECT	 8, 1 UNION ALL
SELECT	 9, 4 UNION ALL
SELECT	10, 2

;WITH yak (ID, Tickets)
AS (
	SELECT	ID,
		Tickets
	FROM	@Sample
	WHERE	Tickets <= 10

	UNION ALL

	SELECT	-1,
		0
)

SELECT TOP 1	NULLIF(y0.ID, -1) AS id1,
		NULLIF(y1.ID, -1) AS id2,
		NULLIF(y2.ID, -1) AS id3,
		NULLIF(y3.ID, -1) AS id4,
		NULLIF(y4.ID, -1) AS id5,
		y0.Tickets,
		y1.Tickets,
		y2.Tickets,
		y3.Tickets,
		y4.Tickets
FROM		yak AS y0
INNER JOIN	yak AS y1 ON y1.ID < y0.ID
INNER JOIN	yak AS y2 ON y2.ID < y1.ID
INNER JOIN	yak AS y3 ON y3.ID < y2.ID
INNER JOIN	yak AS y4 ON y4.ID < y3.ID
WHERE		y0.Tickets + y1.Tickets + y2.Tickets +  y3.Tickets + y4.Tickets = 10
ORDER BY	NEWID()

E 12°55'05.63"
N 56°04'39.26"

dimoss
Yak Posting Veteran

52 Posts

Posted - 2008-11-07 : 12:16:30

quote:

Originally posted by Peso

Or this simpler but slower on a large table

DECLARE	@Sample TABLE
	(
		ID TINYINT NOT NULL,
		Tickets TINYINT NOT NULL
	)

INSERT	@Sample
SELECT	 1, 3 UNION ALL
SELECT	 2, 5 UNION ALL
SELECT	 3, 1 UNION ALL
SELECT	 4, 2 UNION ALL
SELECT	 5, 1 UNION ALL
SELECT	 6, 2 UNION ALL
SELECT	 7, 5 UNION ALL
SELECT	 8, 1 UNION ALL
SELECT	 9, 4 UNION ALL
SELECT	10, 2

;WITH yak (ID, Tickets)
AS (
	SELECT	ID,
		Tickets
	FROM	@Sample
	WHERE	Tickets <= 10

	UNION ALL

	SELECT	-1,
		0
)

SELECT TOP 1	NULLIF(y0.ID, -1) AS id1,
		NULLIF(y1.ID, -1) AS id2,
		NULLIF(y2.ID, -1) AS id3,
		NULLIF(y3.ID, -1) AS id4,
		NULLIF(y4.ID, -1) AS id5,
		y0.Tickets,
		y1.Tickets,
		y2.Tickets,
		y3.Tickets,
		y4.Tickets
FROM		yak AS y0
INNER JOIN	yak AS y1 ON y1.ID < y0.ID
INNER JOIN	yak AS y2 ON y2.ID < y1.ID
INNER JOIN	yak AS y3 ON y3.ID < y2.ID
INNER JOIN	yak AS y4 ON y4.ID < y3.ID
WHERE		y0.Tickets + y1.Tickets + y2.Tickets +  y3.Tickets + y4.Tickets = 10
ORDER BY	NEWID()

E 12°55'05.63"
N 56°04'39.26"

Thank you Peso.

What if I don't have a specific number of id's but only a given number of tickets.

In my example what would be the code for the random draw for a total of 10 tickets?

P.S. I want this code to work on a large table...

Thank you again.

www.tabletennis.gr

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-07 : 13:04:31

If you want a generic solution, why don't you provide that information from the beginning?
Do you think it is fun to have us work twice as hard, for free?

You want a solution that should work for any number of tickets?
See http://weblogs.sqlteam.com/peterl/archive/2008/08/12/How-to-sum-up-an-unknown-number-of-records.aspx

E 12°55'05.63"
N 56°04'39.26"

dimoss
Yak Posting Veteran

52 Posts

Posted - 2008-11-07 : 13:19:27

quote:
Originally posted by Peso

If you want a generic solution, why don't you provide that information from the beginning?
Do you think it is fun to have us work twice as hard, for free?

You want a solution that should work for any number of tickets?
See http://weblogs.sqlteam.com/peterl/archive/2008/08/12/How-to-sum-up-an-unknown-number-of-records.aspx

E 12°55'05.63"
N 56°04'39.26"

Sorry Peso..:-(
I didn't want to make fun.
I just tried to give an example to make it clear.
Sorry again and thanks for the reply.

www.tabletennis.gr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources