June 27, 2012 at 5:51 am
Hi dudes...
i wanna select Nth row of a concatenated column... i wrote this, but it gives me error...
"SELECT TOP 1 test FROM (SELECT TOP 1 (name+cast(id as char)+xtype) as test FROM master..sysobjects ORDER BY name ASC) sq ORDER BY name DESC "
any one can help me in this topic?
Thanks...
June 27, 2012 at 5:56 am
sh3llm4n (6/27/2012)
Hi dudes...i wanna select Nth row of a concatenated column... i wrote this, but it gives me error...
"SELECT TOP 1 test FROM (SELECT TOP 1 (name+cast(id as char)+xtype) as test FROM master..sysobjects ORDER BY name ASC) sq ORDER BY name DESC "
any one can help me in this topic?
Thanks...
[name] is not a column of derived table sq. Try this:
SELECT TOP 1 test
FROM (
SELECT TOP 1 (name+cast(id as char)+xtype) as test
FROM master..sysobjects ORDER BY name ASC
) sq
ORDER BY test DESC
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 27, 2012 at 6:28 am
also, to get the nth value, i'd add row number to the inner query :
declare @n int
SET @n = 4
SELECT sq.test
FROM (
SELECT row_number() over (order by name) As RW,
(name+cast(id as char)+xtype) as test
FROM master..sysobjects
) sq
WHERE sq.rw = @n --the 4th value
ORDER BY sq.test DESC
Lowell
June 27, 2012 at 7:11 am
WITH A as
(SELECT (name+cast(id as char)+xtype) test, row_number() over(order by name asc) rn
FROM master..sysobjects)
SELECT * FROM a where rn = 3 --choose whatever row number you want
-Jake
June 30, 2012 at 11:31 pm
Thanks alot dudes...
π
It works as good as past...
July 1, 2012 at 10:27 am
sh3llm4n (6/30/2012)
Thanks alot dudes...π
It works as good as past...
Heh... "dudes". Not a compliment where I come from. π
--Jeff Moden
Change is inevitable... Change for the better is not.
July 1, 2012 at 10:44 am
Jeff Moden (7/1/2012)
sh3llm4n (6/30/2012)
Thanks alot dudes...π
It works as good as past...
Heh... "dudes". Not a compliment where I come from. π
a colloquial meaning, maybe?
no offence where where I come from...though not the way I would prefer to be addressed...however, saying that, I can confirm that I have been addressed in many ways...several of which I would deem to be far worse than 'dude'.......:w00t:
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply