Stupid SQL Tricks

Finished up a successful engagement as ETL/Data Warehouse developer for a set of Silverlight and iPhone marketing apps. Pretty cool, but less than impressed with the stored procedure support (read: "lack of") in MSFT's ADO.Net Entity Framework. Now I am back in the interview loops and am once again confronted with the bane of my existence, "Stupid SQL Tricks".

These are those crazy SQL problems that I have never actually used in thirty years of professional development experience, but that somebody found in the back of one of Joe Celko's books and thought would make a good interview questions. You know. The ones that involve a boat and a river...

Now I don't know about you, but I am a certifiable, left-brained creative type. Couldn't care less about the how many pinheads can dance on an angel or whatever. So in this post I am going to answer the last one I got, if for no other reason than being able to point the next right-brained analytical-retentive to my blog instead of having to answer the question at the white board.

This is the "what is the second from the top" question, usually worded something like: "You have a table containing names, dates, and amounts. Give me a list of the second most recent transaction by name." Yeah. Like anybody is ever going to actually USE that. So the right answer appears below. But if it gets you a job with a bunch of maniacs, don't blame me. I warned you.

At least, that's how I see it.


--
-- Stupid SQL Tricks
--
-- purpose:
-- select the Nth most-recent row
-- notes:
-- valid values of @n are 1-4
-- [tAmt] corresponds to ordinal position
-- history:
-- 20090918 james atwater created
--
begin

set nocount on

declare @n int;
set @n = 2;

declare @tbl table
( tblID integer not null identity(1,1) primary key
, uName nvarchar(160) not null -- user name
, tDate datetime not null -- transaction date
, tAmt decimal(10,2) not null -- transaction amount
) ;

insert @tbl
( uName , tDate , tAmt )
values ( 'James Joyce', '1/1/2001' , 4.00 )
, ( 'James Joyce', '1/3/2001' , 3.00 )
, ( 'James Joyce', '1/8/2001' , 1.00 )
, ( 'James Joyce', '1/6/2001' , 2.00 )
, ( 'Kathy White', '1/5/2001' , 2.00 )
, ( 'Kathy White', '1/4/2001' , 3.00 )
, ( 'Kathy White', '1/7/2001' , 1.00 )
, ( 'Kathy White', '1/2/2001' , 4.00 )
, ( 'David Green', '1/10/2001', 3.00 )
, ( 'David Green', '1/11/2001', 2.00 )
, ( 'David Green', '1/9/2001' , 4.00 )
, ( 'David Green', '1/12/2001', 1.00 )
;

select *
from @tbl t
where @n - 1 =
( select count(*)
from @tbl tx
where t.uName = tx.uName
and t.tDate < tx.date
) ;

Initialize

Job search is always fun. Helps the prayer life and reminds you of who your friends are. Freelance data geeks like me are almost perpetually in search of the next gig, putting us in league with professional musicians, used car dealers, and politicians.

As such, the act of digging up new clients serves as a gut check on our personal priorities, morals, and values as much as the usual introspections about our dedication to the craft and our personal convictions about one's value to society and the marketplace.

I just went through another one of those fun interview loops where a bunch of people with widely varying agendas and needs talk to you (ostensibly) about what they are looking for and deciding if your skills are a good fit. Often as not this has little if anything to do with the actual position description, as the real needs of the interviewers focus more on their personal career goals as the needs of the employer.

Most important thing to remember is that even when the news says unemplyment is up, I am only looking for one job, a job that not just anyone can do. Second most important is that failing to get hired, even after a successful interview loop, is not my fault. An earnest, honest resume and a willing, charitable heart are all any of us can bring to the table and if that is not enough then you would not want to work for them anyway. Of course, a recent trip to the dentist for a good tooth cleaning can never hurt...

At least that's the way I see it.