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
) ;