how to use a column name in argument of top() ?

Question

how to use a column name in argument of top() ?

descentflower

Ten Centuries

Points: 1227
More actions
June 18, 2009 at 12:56 am

#207165

Hi,
I have been receiving below error when i use top(p.nid) in my select.
error:
The reference to column "nid" is not allowed in the argument of the TOP clause. Only references to columns at an outer scope or standalone expressions and subqueries are allowed here.
BTW my argument to top() is stored in a table and i need to use that.
tbltop
-------------
idnid
12
23
tbldata
-------------
iddata
1a
1b
1c
1d
1e
1f
1g
1h
2a
2b
2c
2d
2e
i used this query but it fails.
select top(t.nid) d.data from tbldata d inner join tbltop t on t.id=d.id

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

descentflower (6/18/2009)
Hi,
I have been receiving below error when i use top(p.nid) in my select.
error:
The reference to column "nid" is not allowed in the argument of the TOP clause. Only references to columns at an outer scope or standalone expressions and subqueries are allowed here.
BTW my argument to top() is stored in a table and i need to use that.
tbltop
-------------
idnid
12
23
tbldata
-------------
iddata
1a
1b
1c
1d
1e
1f
1g
1h
2a
2b
2c
2d
2e
i used this query but it fails.
select top(t.nid) d.data from tbldata d inner join tbltop t on t.id=d.id

top requires an integer to be passed.

top 1 gives the first record based on ur query.

you might want to use max in this case

select max(t.nid) d.data from tbldata d inner join tbltop t on t.id=d.id

group by d.data

Pradeep Singh

WilliamBendall SSCrazy Points: 2591 More actions · Answer 2

Hi,

What output are you looking for? TOP is not a function you can use on a join. As Pradeep says, you can use MAX() but then again, it depends on what the output it is that you require.

----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

descentflower Ten Centuries Points: 1227 More actions · Answer 3

Hi,

i need output in this form,

iddata

1a

1b

2a

2b

2c

top(2) for id=1

top(3) for id=2

ps. One Orange Chip Points: 29422 More actions · Answer 4

descentflower (6/18/2009)
Hi,
top(2) for id=1
top(3) for id=2

still unclear what u need!!

can u post sample data in each tables and expected output.

Pradeep Singh

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

Assuming 2005 +

with ctedata(id,data,rownum)

as

(

Select id,data,row_number() over (partition by id order by data)

from tblData

)

select cteData.id,cteData.data

from cteData,

tbltop

where cteData.id = tbltop.id

and rownum <=tbltop.nid

Clear Sky SQL
My Blog[/url]

Darpan Sarin SSC-Addicted Points: 421 More actions · Answer 6

You can use order by clause with top in select like

select top(2) * from Table order by Id

descentflower Ten Centuries Points: 1227 More actions · Answer 7

Hi all,

here is sample data.

tbltop

-------------

idnid

1 2

2 3

tbldata

-------------

id data

1 a

1 b

1 c

1 d

1 e

1 f

1 g

1 h

2 a

2 b

2 c

2 d

2 e

i need top x rows from table tbldata for each id in tabletop.

Value of x is stored in table tbltop.

Expected output

---------------

iddata

1a

1b

2a

2b

3c

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 8

Did you try my suggestion ?

It gives you that...

Clear Sky SQL
My Blog[/url]

descentflower Ten Centuries Points: 1227 More actions · Answer 9

Dave Ballantyne (6/18/2009)
Did you try my suggestion ?
It gives you that...

:w00t:Hi,Dave Thanks it works as expected by me !! :w00t:

PS have u understood my problem?

Thanks again dave

Paul White SSC Guru Points: 150467 More actions · Answer 10

Ah. I see.

This should do the trick, I think:

-- This table specifies how many rows to return for each row_id in the data table

DECLARE@Top TABLE (row_id INT NOT NULL, top_count INT NOT NULL);

-- The data table. The seq_id column is there to provide a basis for choosing the TOP (n) rows

DECLARE@data TABLE (seq_id INT IDENTITY, row_id INT NOT NULL, data CHAR(1) NOT NULL);

-- Sample top row specifications

-- Three rows for row_id 1, two for row_id 2, and one for row_id 3

INSERT@Top (row_id, top_count)

SELECT1, 3 UNION ALL

SELECT2, 2 UNION ALL

SELECT3, 1;

-- Data to choose from

-- Assumes data will be inserted in the listed order

INSERT@data (row_id, data)

SELECT1, 'a' UNION ALL

SELECT1, 'b' UNION ALL

SELECT1, 'c' UNION ALL

SELECT1, 'd' UNION ALL

SELECT1, 'e' UNION ALL

SELECT1, 'f' UNION ALL

SELECT2, 'A' UNION ALL

SELECT2, 'B' UNION ALL

SELECT2, 'C' UNION ALL

SELECT3, 'Z' UNION ALL

SELECT3, 'Y';

-- The final select

SELECTT.row_id, CA.data

FROM@Top AS T

CROSS

APPLY(

-- This is run for every row selected from T in the outer query

-- Since T is an outer reference, we can use a column from it for the TOP

SELECTTOP (T.top_count)

D.Data

FROM@data AS D

WHERED.row_id = T.row_id

ORDERBY

D.seq_id

) AS CA;

Output:

[font="Courier New"]row_id, data

1a

1b

1c

2A

2B

3Z

[/font]

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 11

PS have u understood my problem?

I believe so , but i would encourage , and expect, you to understand the code and to ensure that the given solution works under all your expected circumstances.

I wont be the one who will be getting a midnight call if it doesnt work as expected and your company is losing money, that'll be you.

Clear Sky SQL
My Blog[/url]

descentflower Ten Centuries Points: 1227 More actions · Answer 12

Dave Ballantyne (6/18/2009)
PS have u understood my problem?
I believe so , but i would encourage , and expect, you to understand the code and to ensure that the given solution works under all your expected circumstances.
I wont be the one who will be getting a midnight call if it doesnt work as expected and your company is losing money, that'll be you.

Sure.

descentflower Ten Centuries Points: 1227 More actions · Answer 13

Paul White (6/18/2009)
Ah. I see.
This should do the trick, I think:
-- This table specifies how many rows to return for each row_id in the data table
DECLARE@Top TABLE (row_id INT NOT NULL, top_count INT NOT NULL);
-- The data table. The seq_id column is there to provide a basis for choosing the TOP (n) rows
DECLARE@data TABLE (seq_id INT IDENTITY, row_id INT NOT NULL, data CHAR(1) NOT NULL);
-- Sample top row specifications
-- Three rows for row_id 1, two for row_id 2, and one for row_id 3
INSERT@Top (row_id, top_count)
SELECT1, 3 UNION ALL
SELECT2, 2 UNION ALL
SELECT3, 1;
-- Data to choose from
-- Assumes data will be inserted in the listed order
INSERT@data (row_id, data)
SELECT1, 'a' UNION ALL
SELECT1, 'b' UNION ALL
SELECT1, 'c' UNION ALL
SELECT1, 'd' UNION ALL
SELECT1, 'e' UNION ALL
SELECT1, 'f' UNION ALL
SELECT2, 'A' UNION ALL
SELECT2, 'B' UNION ALL
SELECT2, 'C' UNION ALL
SELECT3, 'Z' UNION ALL
SELECT3, 'Y';
-- The final select
SELECTT.row_id, CA.data
FROM@Top AS T
CROSS
APPLY(
-- This is run for every row selected from T in the outer query
-- Since T is an outer reference, we can use a column from it for the TOP
SELECTTOP (T.top_count)
D.Data
FROM@data AS D
WHERED.row_id = T.row_id
ORDERBY
D.seq_id
) AS CA;
Output:
[font="Courier New"]row_id, data
1a
1b
1c
2A
2B
3Z
[/font]
Paul

Thanks Paul , this gave me the expected result. But i need to test under different situations.

Which one will be better ? a cross apply or with cte ? i donno what cross apply does.

Paul White SSC Guru Points: 150467 More actions · Answer 14

This is the Books Online link: http://msdn.microsoft.com/en-us/library/ms175156(SQL.90).aspx

This is an article on this site: http://www.sqlservercentral.com/articles/T-SQL/63115/

The APPLY solution should generally slightly out-perform the CTE (although the plans are pretty similar).

The main difference is that the APPLY uses a TOP operator directly, whereas the CTE uses the ROW_NUMBER window function and an inequality to do the same job. The ROW_NUMBER may also require a sort before the segment and sequence project to get the rows into order, unless an appropriately sorted index is available and selected by the optimizer.

The main reason I provided the APPLY solution was because your original question specifically asked how to use a column with the TOP operator 🙂

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi