June 18, 2009 at 12:56 am
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
-------------
id nid
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 used this query but it fails.
select top(t.nid) d.data from tbldata d inner join tbltop t on t.id=d.id
June 18, 2009 at 1:00 am
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
-------------
id nid
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 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
June 18, 2009 at 1:11 am
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
June 18, 2009 at 1:32 am
Hi,
i need output in this form,
id data
1 a
1 b
2 a
2 b
2 c
top(2) for id=1
top(3) for id=2
June 18, 2009 at 1:39 am
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.
June 18, 2009 at 1:42 am
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
June 18, 2009 at 1:55 am
You can use order by clause with top in select like
select top(2) * from Table order by Id
June 18, 2009 at 2:40 am
Hi all,
here is sample data.
tbltop
-------------
id nid
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
---------------
id data
1 a
1 b
2 a
2 b
3 c
June 18, 2009 at 2:47 am
June 18, 2009 at 2:59 am
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
June 18, 2009 at 3:08 am
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)
SELECT 1, 3 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 3, 1;
-- Data to choose from
-- Assumes data will be inserted in the listed order
INSERT @Data (row_id, data)
SELECT 1, 'a' UNION ALL
SELECT 1, 'b' UNION ALL
SELECT 1, 'c' UNION ALL
SELECT 1, 'd' UNION ALL
SELECT 1, 'e' UNION ALL
SELECT 1, 'f' UNION ALL
SELECT 2, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 2, 'C' UNION ALL
SELECT 3, 'Z' UNION ALL
SELECT 3, 'Y';
-- The final select
SELECT T.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
SELECT TOP (T.top_count)
D.Data
FROM @Data AS D
WHERE D.row_id = T.row_id
ORDER BY
D.seq_id
) AS CA;
Output:
[font="Courier New"]row_id, data
1 a
1 b
1 c
2 A
2 B
3 Z
[/font]
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 18, 2009 at 3:23 am
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.
June 18, 2009 at 3:30 am
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.
June 18, 2009 at 3:36 am
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)
SELECT 1, 3 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 3, 1;
-- Data to choose from
-- Assumes data will be inserted in the listed order
INSERT @Data (row_id, data)
SELECT 1, 'a' UNION ALL
SELECT 1, 'b' UNION ALL
SELECT 1, 'c' UNION ALL
SELECT 1, 'd' UNION ALL
SELECT 1, 'e' UNION ALL
SELECT 1, 'f' UNION ALL
SELECT 2, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 2, 'C' UNION ALL
SELECT 3, 'Z' UNION ALL
SELECT 3, 'Y';
-- The final select
SELECT T.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
SELECT TOP (T.top_count)
D.Data
FROM @Data AS D
WHERE D.row_id = T.row_id
ORDER BY
D.seq_id
) AS CA;
Output:
[font="Courier New"]row_id, data
1 a
1 b
1 c
2 A
2 B
3 Z
[/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.
June 18, 2009 at 3:48 am
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://qa.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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply