select row with 3 odd 2 even numbers

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 2005 Forums

Transact-SQL (2005)

select row with 3 odd 2 even numbers

Author

Topic

ritexcorp
Starting Member

3 Posts

Posted - 2009-03-11 : 12:44:58

am writing a lottery program and i need to select a result that has a specified number of odd and even results. if 3 odd are specified in a form, naturally, there will be 2 even in the result for a 5 number game and vica versa.
I am selecting from a table that has 5 columns and a rowID, with a number in each column. I am wondering is there a way to select a specified number of Odd/even in my result using purely sql?

**psuedocode**
Select * from SUM61TBL
where numcount(odd % 2)=2

Select * from `#form.sum#`
order by Rand()
limit #form.quantity#

!@#$%

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 12:54:14

Yes there is. Please provide proper sample data.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 12:55:20

Odd number
select * from table1 where id % 2 = 1

Even number
select * from table1 where id % 2 = 0

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

ritexcorp
Starting Member

3 Posts

Posted - 2009-03-11 : 13:31:51

that will give me an odd or even rowID. What i want is a specified nuber of Odd/even numbers in the resultset
//This is unsorted data
Sample Date:

2 3 5 10 11
1 2 6 9 13
1 5 6 7 12
1 3 4 10 13

Sample table SUM31TBL:

RowID Num1 Num2 Num3 Num4 Num5

after applying my odd even filter lets say I want results with only 3 odd and 2 even
2 3 5 10 11
1 2 6 9 13
etc.

likewise I want to be able to select diferent options 4 Odd 1, 1 even 5 odd 0 even, etc.
a case statement in my code should be able to handle this logic with the appropriate select statement delivering the result, but how to write the SQL??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 03:54:50

[code]DECLARE @Sample TABLE
(
col1 INT,
col2 INT,
col3 INT,
col4 INT,
col5 INT
)

INSERT @Sample
SELECT ABS(CHECKSUM(NEWID())) % 20,
ABS(CHECKSUM(NEWID())) % 20,
ABS(CHECKSUM(NEWID())) % 20,
ABS(CHECKSUM(NEWID())) % 20,
ABS(CHECKSUM(NEWID())) % 20
FROM master..spt_values
WHERE Type = 'P'

SELECT @@ROWCOUNT AS [Records inserted]

SELECT col1,
col2,
col3,
col4,
col5
FROM @Sample
WHERE col1 % 2 + col2 % 2 + col3 % 2 + col4 % 2 + col5 % 2 = 3

SELECT @@ROWCOUNT AS [Records returned][/code]

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-03-12 : 06:38:46

moved from script library forum.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

ritexcorp
Starting Member

3 Posts

Posted - 2009-03-12 : 19:23:32

Wow this is a jaw breaker, i will give it a whirl.
I had this:
Select * from `31`
where ( num1 % 2 = 0 and num2 % 2 = 1 and num3 % 2 = 0 and num4 % 2 = 0 and num5 % 2 = 0 );

this gives me 4 even and 1 odd set(s) or rows, but has one problem.
I am specifying the column for the odd number as column 2. What happens if the one Odd number
is in column one, or 3 or 4 or 5. if only i could assign the column as a variable and iterate from column to column writing the result that i want to a new table.
into a new table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-13 : 03:24:29

You have to set a comparison value for your WHERE clause.

SELECT	col1,
	col2,
	col3,
	col4,
	col5
FROM	@Sample
WHERE	col1 % 2 + col2 % 2 + col3 % 2 + col4 % 2 + col5 % 2 = @NumbersOfOddColumnsWanted

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources