Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL teaser return 5th item 3

Status
Not open for further replies.
SQLDenis

SQLDenis

Programmer
Oct 1, 2005
5,575
US
Code: 
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
 
Sort by date Sort by votes
Select top 1 b.id from #Teaser b join #Teaser a on b.id=8

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]
 
Upvote 0 Downvote
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
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
[lol] you copied Paul

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]
 
Upvote 0 Downvote
[wink]
I was hoping you had the correct answer!

- Paul
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
What? You can't change the rules ;-)

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]
 
Upvote 0 Downvote
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:

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
 
Upvote 0 Downvote
Ah, with the rule change, I resubmit my second option ;-)

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
I got it to 64. Nice one Mark :)

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
I had this in mind, should not be used in production code of course
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

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
 
Upvote 0 Downvote
But, everybody knows that TOP is no good without ORDER BY.

SO:

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
 
Upvote 0 Downvote
What about this...
[smile]

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
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
336
pjw001
pjw001
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
362
Volvere
V
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
231
Mike Lewis
Mike Lewis
grnzbra
  • Locked
  • Question
T-SQL Dialog Box
Replies
8
Views
205
Chris Miller
Chris Miller
Andrzejek
  • Locked
  • Question
Eliminate pesky [ ] in SQL Management Studio
Replies
5
Views
254
SaltyTheFrog
SaltyTheFrog

Part and Inventory Search

Sponsor

Back
Top