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?
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.