Select Top With inline query - SQL Server Forums

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

Transact-SQL (2000)

Select Top With inline query

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-06-07 : 10:56:34

If I write a query some thing like this, it doesnt work in sql server 2000, but works in sql sever 2005.

Select top (Select NoOfSelections From @Test Where RowId = @Counter ) AL.Score From AnswerLibrary AL
Inner Join @Test Tmp on Tmp.QuestionId = AL.QuestionID Where RowId = @Counter Order By AL.Score Desc

I get an error message saying incorrect synatax near (
Any body can say whether this kind of query is possible in sql 2000 or not?

Thanks

Karunakaran

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-06-07 : 11:14:41

declare @rowCount int
Select @rowCount = NoOfSelections From @Test Where RowId = @Counter
set rowcount @rowCount

Select AL.Score From AnswerLibrary AL
Inner Join @Test Tmp on Tmp.QuestionId = AL.QuestionID Where RowId = @Counter Order By AL.Score Desc

set rowcount 0

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-07 : 11:17:28

It's not. You can do something like this instead...

declare @NoOfSelections int
Select @NoOfSelections = NoOfSelections From @Test Where RowId = @Counter

set rowcount @NoOfSelections

Select AL.Score From AnswerLibrary AL 
Inner Join @Test Tmp on Tmp.QuestionId = AL.QuestionID Where RowId = @Counter Order By AL.Score Desc

set rowcount 0

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-07 : 11:18:20

Doh! Nice work spirit...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-06-07 : 11:25:49

there a special word we use when this happens

it's called sniping

= [s n i p e d] without spaces

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-06-07 : 11:29:21

Thanks Spirit & Ryan...

Karunakaran

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-07 : 11:30:29

I was looking for that! I will add it to my favorites ready for next time...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-06-07 : 11:46:37

Now I'm getting this error
Server: Msg 443, Level 16, State 2, Procedure udfMaxScore, Line 28
Invalid use of 'UNKNOWN TOKEN' within a function.

Wherever I have used set rowcount, that line gives error.

The full code:


CREATE FUNCTION dbo.udfMaxScore
(
	-- Add the parameters for the function here
	@QSetID int,@GroupID int
)
RETURNS int
AS
BEGIN
	-- Declare the return variable here
Declare @Score int, @Counter int,@NoOfQuestions int,@TotalScore int 
Declare @Test Table (RowId int Identity(1,1), QuestionId int, Score int,NoOfSelections int,GroupId int)
Declare @rowcount int
Set @Counter = 1

Select @NoOfQuestions = count(distinct questionid) from qsetdetail where qsetid = @QSetID
--Create Table @Test (RowId int Identity(1,1), QuestionId int, Score int,NoOfSelections int)
If @GroupID Is Null
Begin
Insert into @Test (QuestionId,Score,NoOfSelections)
Select distinct QL.QuestionId,Null,NoOfSelections from AnswerLibrary AL Inner Join 
QuestionLibrary QL on QL.QuestionId = AL.QuestionId  
Inner Join QsetDetail QD on QD.QuestionId = AL.QuestionId
Where Qsetid = @QSetID
Select @NoOfQuestions = count(questionid) from @Test
While @Counter <= @NoOfQuestions
Begin
Select @rowcount = NoOfSelections From @Test Where RowId = @Counter
set rowcount @rowcount
Update @Test Set Score = (Select Sum(Score) as Score From 
(
Select Top 100 percent AL.Score From AnswerLibrary AL 
Inner Join @Test Tmp on Tmp.QuestionId = AL.QuestionID Where RowId = @Counter Order By AL.Score Desc
)Temp) Where RowId = @Counter
set rowcount 0
Set @Counter = @Counter+1
End
Select @TotalScore = Sum(Score) from @Test
End
RETURN @TotalScore
END

Thanks

Karunakaran

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-07 : 11:52:44

You can't do this in a function. I think you should either do it in a stored procedure instead, or you should take a step back and describe what it is you are trying to achieve overall (by supplying DDL, sample data, expected results for that sample data etc).

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-06-07 : 12:47:44

[code]
declare @ql table
(
qid int,
qdesc varchar(1000),
noofselections int
)
insert into @ql
select 1,'test',3
union all
select 2,'test2',2

declare @al table
(
aid int identity(1,1),
qid int,
answer varchar(200),
score int
)
insert into @al
select 1,'ans1',3
union all
select 1,'ans1',1
union all
select 1,'ans1',5
union all
select 1,'ans1',8
union all
select 1,'ans1',4
union all
select 2,'ans2',5
union all
select 2,'ans2',9
union all
select 2,'ans2',1
[/code]
[code]
Final output would be
qid score
1 17 ( 8+5+4) This is the sum top 3 (which is the value of noofselections) score desc
2 14 (9+5) This is the sum top 2 (which is the value of noofselections) score desc [/code]

Thanks

Karunakaran

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-06-07 : 13:14:35

here you go. but it's not preety


declare @ql table
(
qid int,
qdesc varchar(1000),
noofselections int
)
insert into @ql
select 1,'test',3
union all
select 2,'test2',2

declare @al table
(
aid int identity(1,1),
qid int,
answer varchar(200),
score int
)
insert into @al
select 1,'ans1',3
union all
select 1,'ans1',1
union all
select 1,'ans1',5
union all
select 1,'ans1',8
union all
select 1,'ans1',4
union all
select 2,'ans2',5
union all
select 2,'ans2',9
union all
select 2,'ans2',1

select * from @ql
select * from @al

select t3.qid, sum(t4.score)
from @ql t3 join
		(	select rank = count(*), t1.qid, t1.score
			from @al t1 join @al t2 on t1.qid + t1.score >= t2.qid + t2.score
			group by t1.qid, t1.score) t4 on t3.qid = t4.qid
where t4.rank > t3.noofselections
group by t3.qid

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-07 : 13:15:53

Something like this...

--data
declare @ql table (qid int, qdesc varchar(1000), noofselections int)
insert into @ql
          select 1,'test',3
union all select 2,'test2',2

declare @al table (aid int identity(1,1), qid int, answer varchar(200), score int)
insert into @al
          select 1,'ans1',3
union all select 1,'ans1',1
union all select 1,'ans1',5
union all select 1,'ans1',8
union all select 1,'ans1',4
union all select 2,'ans2',5
union all select 2,'ans2',9
union all select 2,'ans2',1

--calculation
select a.qid, sum(score) as score from @al a
  inner join @ql b on a.qid = b.qid and b.noofselections >= 
      (select count(*) from @al where qid = a.qid and score >= a.score)
group by a.qid

/*results
qid         score       
----------- ----------- 
1           17
2           14
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-06-07 : 14:16:45

Thanks Again.

Karunakaran

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-06-29 : 11:16:47

This query is failing for this set of records, I tried to see whats going wrong. I couldnt find anything

. Otherwise it works fine.


declare @al table (aid int, qid int, score int)
insert into @al
Select 301,1,105 union all
Select 302,0,105 union all
Select 303,0,106 union all
Select 304,0,106 union all
Select 305,1,106 union all
Select 306,0,106 union all
Select 307,1,107 union all
Select 308,1,107 union all
Select 309,1,107 union all
Select 310,1,107 union all
Select 311,1,107 union all
Select 312,1,107 union all
Select 313,1,107 union all
Select 314,1,107 union all
Select 315,0,107 union all
Select 316,0,108 union all
Select 317,0,108 union all
Select 318,0,108 union all
Select 319,0,108 union all
Select 320,0,108 union all
Select 321,0,108 union all
Select 322,0,108 union all
Select 323,0,108 union all
Select 324,0,108 union all
Select 325,0,108 union all
Select 326,1,109 union all
Select 327,1,109 union all
Select 328,1,109 union all
Select 329,1,109 union all
Select 330,1,109 union all
Select 331,1,109 union all
Select 332,1,109


declare @ql table (qid int,noofselections int)
insert into @ql
Select 105,1 union all
Select 106,1 union all
Select 107,1 union all
Select 109,1 union all
Select 108,4

select a.qid, sum(score) as score from @al a
  inner join @ql b on a.qid = b.qid and b.noofselections >= 
      (select count(*) from @al where qid = a.qid and score >= a.score)
group by a.qid

Expected Result is:
qid score
105  1
106  1
107  1
108  0
109  1

Thanks
Karunakaran

Karunakaran

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-29 : 11:57:28

The 1st issue is that the data is the wrong way round in @al.

Once that's sorted, there is still a problem, though: It seems the code does not cope with ties.

This is tricky at first thought, but let's see what people come up with (I'll have a go myself)...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-06-29 : 12:01:51

quote:
Originally posted by RyanRandall

The 1st issue is that the data is the wrong way round in @al.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Oops....

Here is the revised one for @al


declare @al table (aid int, score int, qid int)
insert into @al
Select 301,1,105 union all
Select 302,0,105 union all
Select 303,0,106 union all
Select 304,0,106 union all
Select 305,1,106 union all
Select 306,0,106 union all
Select 307,1,107 union all
Select 308,1,107 union all
Select 309,1,107 union all
Select 310,1,107 union all
Select 311,1,107 union all
Select 312,1,107 union all
Select 313,1,107 union all
Select 314,1,107 union all
Select 315,0,107 union all
Select 316,0,108 union all
Select 317,0,108 union all
Select 318,0,108 union all
Select 319,0,108 union all
Select 320,0,108 union all
Select 321,0,108 union all
Select 322,0,108 union all
Select 323,0,108 union all
Select 324,0,108 union all
Select 325,0,108 union all
Select 326,1,109 union all
Select 327,1,109 union all
Select 328,1,109 union all
Select 329,1,109 union all
Select 330,1,109 union all
Select 331,1,109 union all
Select 332,1,109

Karunakaran

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-29 : 12:43:52

Karunakaran - Here's my revised effort...

select a.qid, sum(score) as score from @ql a
  left outer join @al b on a.qid = b.qid and a.noofselections >= 
      (select count(*) from @al where qid = b.qid and (score > b.score or (score = b.score and aid >= b.aid)))
group by a.qid

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-06-29 : 13:29:17

That works great...

Thanks Ryan

Karunakaran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources