SQL teaser return 5th item 3

[SQLDenis]

create table #Teaser(id int)
insert #Teaser values(1)
insert #Teaser values(3)
insert #Teaser values(5)
insert #Teaser values(6)
insert #Teaser values(8)
insert #Teaser values(12)
insert #Teaser values(14)
insert #Teaser values(18)

return the 5th row in this table (value 8) without using a WHERE clause or a SET ROWCOUNT

shortest code wins

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[ptheriault]

I'm just trying to work around the rules that have been bound to us...

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ca8msm]

declare @i int select top 5@i=id from #Teaser print @i

I guess a print doesn't count?


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[ca8msm]

Spoiler

select distinct 8 from #Teaser

____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[ca8msm]

On a more serious attempt, i've got it down to 48:

Spoiler

Select max(id) from(select top 5* from #Teaser)a

____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[SQLDenis]

star for you

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[Gooser]

Mark & Denis,

Big STAR for both of you! This is exactly what I came to Tek-Tips for today. EXACTLY. Okay, so I need the 36,000th item, not the 5th, but same damn thing, right?



v/r

Gooser

Why do today
that which may not need to be done tomorrow --me

The Universal Model

 

[SQLDenis]

Thanks, use Mark's solution, I wouldn't put mine on a production box because it could break in a future release, for a teaser it is okay ;-)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[ESquared]

Hey, the original stipulation is the fifth ROW not the value of the only column in the fifth row. So even though the test table has only one column, you're cheating if you're getting id into a variable and selecting that! Some answers here won't work if the table has more than one column.

Having not read the other answers, I might be duplicating someone else's work:

Spoiler

SELECT TOP 1 * FROM (SELECT TOP 5 * FROM #Teaser ORDER BY ID) X ORDER BY ID DESC

--and if I am allowed to cheat with ON as a substitute for DECLARE, some interesting (to me) methods
   @Num int,
   @ID int
SET @Num = 1
SELECT @ID = CASE WHEN @Num = 5 THEN ID ELSE @ID END, @Num = @Num + 1 FROM #Teaser ORDER BY ID
SELECT T.*
FROM
   #Teaser T
   INNER JOIN (SELECT ID = @ID) X ON T.[ID] = X.[ID]

SELECT Num = identity(int, 1, 1), [ID] INTO #Number FROM #Teaser ORDER BY ID
SELECT T.*
FROM
   #Teaser T
   INNER JOIN #Number N ON T.ID = N.ID
   INNER JOIN (SELECT Num = 5) X ON N.Num = X.Num
DROP TABLE #Number

Hey, you can nest these things!

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

Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.

 

[SQLDenis]

Spoiler

Spoiler

Spoiler

Spoiler

Spoiler

Spoiler

Hey, you can nest these things!

Yes you can

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[ESquared]

er, I meant to say

If I am allowed to cheat with ON as a substitute for WHERE

Obviously my brain was elseWHERE.

Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.

 

[ESquared]

Oh, now I see... my DECLARE got in the middle of my comment. That's what I get for careless copying and pasting!

Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.

 

[AtomicWedgie]

Hi,

as long as the valus are distinct this should also work. Then at least the order of the numbers should not matter.

select *
from
(select top 5 * from #teaser
union all
select top 4 * from #teaser) x
group by id
having count(*) = 1

Atomic Wedgie


i also tried

select top 5 * from #teaser
except
select top 4 * from #teaser

but could not get it working

 

[tbode2006]

Hi guys,
I've seen these teaser threads before. Couple of questions.
How do you invoke the spoiler cover, and more importantly, how do you/I ever get to see the answers and learn?

 

[ca8msm]

To use the spoiler tag, simply wrap your code inside [ignore][/ignore] tags. To reveal the answer highlight the spoiler section with with your mouse as though you were going to copy the text.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[gmmastros]

You can create a spoiler by using TGML tags. Like this...

[ignore]

Spoiler

This will be hidden

[/ignore]

To reveal the hidden message, simply highlight the text within. You can also double click within the spoiler box. Spoilers simply work by drawing white text on a white background.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[earthandfire]

You can also double click within the spoiler box.

George, you need to triple-click, not double-click to reveal the entire text of the Spoiler.