Subquery with top keyword inside the case statemen

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

.NET Inside SQL Server (2005)

Subquery with top keyword inside the case statemen

Author

Topic

VipinMitta
Starting Member

12 Posts

Posted - 2010-03-16 : 09:31:55

hi all,

i m trying to do as below.

create table table_name(a int, b int)

insert into table_name values(1,1)
insert into table_name values(1,2)
insert into table_name values(1,3)
insert into table_name values(1,5)
insert into table_name values(1,6)
insert into table_name values(1,8)

select case when sum(a+b) > 30 then
(select top 4 a+b from table_name order by a+b )
else
(select top 2 a+b from table_name order by a+b ) end a
from table_name

but above query gives error

NOTE -- i want output in single query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 09:34:20

can you restate your requirement? are you looking at total sum > 30 or individual record sum?
also are you using sql 2005?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

VipinMitta
Starting Member

12 Posts

Posted - 2010-03-17 : 00:37:24

Yyes , im looking at total sum(a+b) > 30

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 01:39:50

[code]SELECT a,b
FROM
(
SELECT a,b,ROW_NUMBER() OVER(ORDER BY b) AS Seq,SUM(a+b) OVER() AS Tot
FROM Table
)t
WHERE (Tot > 30 AND Seq <= 4)
OR Seq <= 2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

VipinMitta
Starting Member

12 Posts

Posted - 2010-03-18 : 03:39:16

Thanks visakh16
Its working fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources