SQL Teaser 2: Rank 1

[SQLDenis]

Shortest code wins and gets a purple star. do not post your solutions yet, just post the length of your SQL statement

you can use SQL server 2005 windowing functions, temp tables whatever you like

here is the table

create table blah2 (Col1 varchar(666) primary key clustered)
insert blah2 values('AAAAA')
insert blah2 values('ABBBBB')
insert blah2 values('ACCCCC')
insert blah2 values('BAAAAA')
insert blah2 values('BBBBBB')
insert blah2 values('BCCCCC')

expected output

Col1	Row
AAAAA	1
ABBBBB	1
ACCCCC	1
BAAAAA	2
BBBBBB	2
BCCCCC	2

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

 

[AlexCuse]

you guys are tricky so and so's ;-)

Ignorance of certain subjects is a great part of wisdom

 

[monksnake]

I got this one in 50 characters.

<.

 

[monksnake]

Make it 49, col starts at 1, not 0. Too many Javascript arrays.

<.

 

[SQLDenis]

>>Make it 49, col starts at 1, not 0. Too many Javascript arrays.


select len('your query here') will give you the length

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

 

[monksnake]

Yeah, it was 49 with the len function. I like these puzzles Denis.

<.

 

[SQLDenis]

Spoiler

select*,ascii(col1)-64 Row from blah2

select len('select*,ascii(col1)-64 Row from blah2') --37

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

 

[gmmastros]

Denis,

You have an extra space where you don't need it.

Spoiler

select*,ascii(col1)-64Row from blah2

-George

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

 

[SQLDenis]

I see, star for that

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

 

[DanEvans]

I got it in 44.

Thanks,
Dan

 

[gmmastros]

Dan,

What did you end up with?

-George

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

 

[DanEvans]

How do I share the code without making it visible? My solution was a hack, btw. It just happens to work, but wouldn't work automatically if you added, say, "CAAAA" and "CBBBB" to the table. Still, it DID produce the results desired, though I wouldn't give me a star for it.

Thanks,
Dan

 

[ptheriault]

use these tags

[ignore]

Spoiler

Your results

[/ignore]

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

 

[gmmastros]

It should also be mentioned that highlighting the text within spoiler tags will reveal the hidden message.

-George

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

 

[DanEvans]

Sorry, got this to 41.

Spoiler

select col1,ascii(col1)-64 row from blah2

Like I said, this won't work for all values of col1, but I think will work for A-Z.

Thanks,
Dan

 

[gmmastros]

Good work. That's almost what we came up with. Now, instead of returning col1, return [!]*[/!] instead. Then play around a little more to see which spaces can be removed. You'll be surprised at which ones can go.

-George

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

 

[DanEvans]

Duh...you're right.

Spoiler

select*,ascii(col1)-64 row from blah2

. Down to 37.

Thanks,
Dan

 

[gmmastros]

There is one more space that you can remove. It's add, but works. Keep going.

-George

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

 

[DanEvans]

Wow, 36. Can't believe that works.

Spoiler

select*,ascii(col1)-64row from blah2

Thanks,
Dan

 

[gmmastros]

That's exactly what I thought too.

-George

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