How to select random rows whose column sum meets the condition in Sql server

Is it possible to select random rows from a table whose particular column total (sum) should be less than my condition value ?

My table structure is like -
id | question | answerInSec
1  | Quest1   | 15
2  | Quest2   | 20
3  | Quest3   | 10
4  | Quest4   | 15
5  | Quest5   | 10
6  | Quest6   | 15
7  | Quest7   | 20

Open in new window


I want to get those random questions whose total sum of 'answerInSec' column is less than (nearest total) or equal to 60.

So random combination can be [1,2,3,4] OR [2,3,5,7] OR [4,5,6,7] etc.
Chaitanya VAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Please try this -

CREATE TABLE randomAns
(
	  id INT ,
	  question VARCHAR(100),
	  answerInSec INT
)
GO

INSERT INTO randomAns VALUES
(1,'Quest1',15),
(2,'Quest2',20),
(3,'Quest3',10),
(4,'Quest4',15),
(5,'Quest5',10),
(6,'Quest6',15),
(7,'Quest7',20)
GO

SELECT * FROM 
(
	SELECT * , SUM(answerInSec) OVER() cnt 
	FROM 
	(
		SELECT TOP 4 *  
		FROM randomAns 
		ORDER BY NEWID()
	)u
)o
WHERE o.cnt <= 60 

Open in new window


OUTPUT 1

/*------------------------
OUTPUT 
------------------------*/
id          question                                                                                             answerInSec cnt
----------- ---------------------------------------------------------------------------------------------------- ----------- -----------
6           Quest6                                                                                               15          60
1           Quest1                                                                                               15          60
7           Quest7                                                                                               20          60
5           Quest5                                                                                               10          60

(4 row(s) affected)

Open in new window


OUTPUT 2

/*------------------------
OUTPUT
------------------------*/
id          question                                                                                             answerInSec cnt
----------- ---------------------------------------------------------------------------------------------------- ----------- -----------
2           Quest2                                                                                               20          60
7           Quest7                                                                                               20          60
5           Quest5                                                                                               10          60
3           Quest3                                                                                               10          60

(4 row(s) affected)

Open in new window


OUTPUT 3

/*------------------------
OUTPUT
------------------------*/
id          question                                                                                             answerInSec cnt
----------- ---------------------------------------------------------------------------------------------------- ----------- -----------
4           Quest4                                                                                               15          55
3           Quest3                                                                                               10          55
2           Quest2                                                                                               20          55
5           Quest5                                                                                               10          55

(4 row(s) affected)

Open in new window


It may also possible that you will no rows at all as everything is random.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chaitanya VAuthor Commented:
I don't want to use Top is it possible with out that?
0
Pawan KumarDatabase ExpertCommented:
Highly No.  What exactly you wanted to achieve with this ?
0
ZberteocCommented:
I don't want to use Top is it possible with out that?
How do you think will SQL server guess how many rows out of all in the table to return if you don't specify the number? In you example you used combination of 4, how would you do that without specifying that you need only 4 rows?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.