Reach for the Select *s

Categories: News, Professional, SSC
Comments: No Comments
Published on: October 12, 2011

Do you have your head in the clouds?

Have you wanted to take flight?

Have you been called “Space Case”

If you are a DB Professional, then all of these could very well become true.

 

You see, there is a very cool thing that is launching today, Oct 12, 2011 by our friends at RedGate.

DBA’s In Space

Ok, so that is not what they are actually calling it.  But just think about it for a minute.  Do you want to be a real space Case as you code the following while glancing back at Earth?

[codesyntax lang=”tsql”]

[/codesyntax]

Not only will your head be in the clouds, but you will exceed Cloud 9 as you take orbit.

I could only dream of what that must feel like (for now anyway).  Being weightless, writing SQL in space and peering back at the blue marble I call home.

Are you hooked yet?

Well, good because here is what RedGate says they are sending a DBA into Space because “they are the real masters of the universe”.  And here are a few more words from our sponsors about the whole contest (and yes there is a contest).

DBA’s must answer 14 questions (yes blue is my favorite color).

DBA’s must be physically fit

The questions will be revealed in video format, via corny B-films.

Fifteen finalists will be selected from those who provide the best answers to quizzes within the videos and the ripest tweets from dataspace. The winning DBA will be elected by popular vote to join the first citizens of Earth to sail beyond our atmosphere.”

And to cap it off, RedGate said it best in their release statement.  Quoting from it:

“Why a DBA? Because they’re the most important people you’ve never heard of. The world is not kept spinning on its course by politicians or financiers. It’s the humble DBA who makes it happen – the Master of Data who enables medical records to be summoned in the blink of an eye, keeps transport running smoothly, manages the data beneath the electrical grid for billions of people and provides instant access to news, music, phone calls, money and an endless supply of entertainment.

So, knowing how DBAs dig space and everything related to it, Red Gate is going to send one of them straight up, a cat’s whisker above the Kármán line – at 62 miles, the official boundary of space – into pitch black, gazing back on the impossibly cool view of planet earth, the view that astronaut Edgar Mitchell called “a glimpse of divinity.””

So check out the website today and get ready to be launched out of this world.

You can find more info at DBAInSpace.com.

An Interesting Sort

Tags: ,
Comments: 7 Comments
Published on: October 12, 2011

I just came across a pretty peculiar sort requirement.  The requirement made me sit and think a bit.  Since it was somewhat peculiar, I decided I would share the solution.

So, let’s start with a little sample data, and then I can go over the requirements.

[codesyntax lang=”tsql”]

[/codesyntax]

Now, we only really have one field that is sortable in this dataset.  And as the title of this post alludes, the sort of that field is not straight forward.  For this data, we need to have the results sorted alpha first and then numeric.

I looked at this and thought, that should be fixed (based on the data) by simply adding a ‘DESC’ to the order by.  Oh but not, that is not entirely accurate.  More test data was added to the sample set with more requirements.  So let’s expand the data set first.

[codesyntax lang=”tsql”]

[/codesyntax]

With this expanded data, it becomes obvious that a simple ‘DESC’ will not fix the issue.  That would place anything the XYZ entry at the top of the list.  But wait, take a look at the second Alpha sequence in the strings.  That complicates things a tiny bit more.  That second alpha sequence also has to be sorted ahead of anything that is numeric.  To further complicate it – it must be in ASC order alpha then numeric as well.

TaDa

So, with a little testing and a nifty trick I was able to come up with something that works.  Let’s take a look at it.

[codesyntax lang=”tsql”]

[/codesyntax]

You will see that I have three conditions in my Order By clause.  Two of those contain a case statement.  By checking to see if something is numeric, I can make sure alpha is placed before numeric.  By including the middle condition, I was able to ensure the correct order for the first alpha sequence.  Without this middle condition, the Alpha strings were all returned before the numeric, but the Alpha was not ordered properly.

Recap

Despite some really odd strings to be ordered and out of the ordinary sorting requirements, it is possible with a little thinking.  My biggest friend here in this requirement was the use of the case statement.  Using the CASE really helped to simplify what I needed to achieve.

page 1 of 1

Calendar
October 2011
M T W T F S S
« Sep   Nov »
 12
3456789
10111213141516
17181920212223
24252627282930
31  

Welcome , today is Tuesday, August 20, 2019