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

 

[onpnt]

Spoiler

Select top 1 b.id from #Teaser b join #Teaser a on b.id=8

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[ptheriault]

Spoiler

I know this isn't what you were thinking because I had to hard code the value.

SELECT a.id
FROM #Teaser a
JOIN #Teaser b on a.id = b.id and a.id = 8

- Paul

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

 

[onpnt]

you copied Paul

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[ptheriault]

I was hoping you had the correct answer!

- Paul

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

 

[SQLDenis]

Okay people no WHERE or ON clause
id = 8 is not allowed

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

 

[onpnt]

What? You can't change the rules ;-)

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[AlexCuse]

This just seems dirty, but since its' asked by the same guy who's solution to 'Without using ASCII Character values' was 'using Unicode character values', I think it is on the right track ;-)

2 options, equally distasteful:

Spoiler

select a.* from #Teaser a inner join #Teaser b on a.id = b.id and a.id = 8

select a.* from #Teaser a inner join #Teaser b on a.id >= b.id group by a.id having count(b.id) = 5

Ignorance of certain subjects is a great part of wisdom

 

[ca8msm]

Spoiler

Select top 1 id from (select top 5 id from #Teaser) a order by id desc

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

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

 

[AlexCuse]

Ah, with the rule change, I resubmit my second option ;-)

Ignorance of certain subjects is a great part of wisdom

 

[SQLDenis]

I got it down to 68 characters

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

 

[SQLDenis]

and 55 if I really cheat

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

 

[AlexCuse]

I got it to 64. Nice one Mark

Ignorance of certain subjects is a great part of wisdom

 

[ca8msm]

I can cut my above example down to 66:

Spoiler

Select top 1 * from (select top 5 * from #Teaser)a order by 1 desc

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

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

 

[SQLDenis]

I had this in mind, should not be used in production code of course

Spoiler

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

select len('declare @i int select top 5@i=id from #Teaser select @i') --55

a little better would be

Spoiler

declare @i int select top 5@i=id from #Teaser order by 1 select @i

select len('declare @i int select top 5@i=id from #Teaser order by 1 select @i') --66

I can trim both by another 3 charaters but I won't show you because then you would know my neXt teaser ;-)

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

 

[AlexCuse]

But, everybody knows that TOP is no good without ORDER BY.

SO:

Spoiler

Select top 1* from(select top 5* from #Teaser order by 1 asc)a order by 1 desc

Ignorance of certain subjects is a great part of wisdom

 

[ca8msm]

I didn't realise you could cut that extra space out Alex (1* instead of 1 *)!


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

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

 

[ptheriault]

What about this...

Spoiler

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)

DELETE #Teaser
WHERE id not in (8)

SELECT *
FROM #Teaser

DROP TABLE #Teaser

- Paul

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

 

[ca8msm]

id = 8 is not allowed

Nice try though Paul!


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

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

 

[SQLDenis]

Paul add this line

insert #Teaser values(null)

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