How to Get the Last 5 Rows from SQL Server

"And hear, I think, the very latest counsel"
-- Shakespeare, Henry IV Part 2
Act IV, Scene V, Line 182

Intro

Say you have the following need.

Given the following trans table:

CREATE TABLE trans (
   id int identity(1,1) not null
  ,transactionId int not null
  ,transactionTime datetime2 not null default (sysutcdatetime())
  ,amount int not null
);

  And the following data:

INSERT INTO trans (transactionId, amount) VALUES(1, 100);
INSERT INTO trans (transactionId, amount) VALUES(1, 125);
INSERT INTO trans (transactionId, amount) VALUES(2, 400);
INSERT INTO trans (transactionId, amount) VALUES(4, 25);
INSERT INTO trans (transactionId, amount) VALUES(5, 20);
INSERT INTO trans (transactionId, amount) VALUES(6, 70);
INSERT INTO trans (transactionId, amount) VALUES(7, 100);
INSERT INTO trans (transactionId, amount) VALUES(8, 33);

** Note, done this way to have different transactionTime **

When I query the trans table

Then I want to get the last 5 records in the trans table as indicated by transactionTime

This setup will give us the following.

http://sqlfiddle.com/#!6/11a33/2

Luckily, SQL Server 2012 offers a few ways to look at the last few records of a table.

TOP N

SELECT TOP 5 *
  FROM trans
  ORDER BY transactionTime DESC
;

By ordering by the transactionTime in descending order we are able to use the TOP filter to obtain the last 5 records.

http://sqlfiddle.com/#!6/11a33/1

ORDER BY with FETCH and OFFSET

SELECT *
  FROM trans
  ORDER BY transactionTime DESC
  OFFSET 0 ROWS
  FETCH NEXT 5 ROWS ONLY
;

Using the ORDER BY clause, we can use the new to SQL Server 2012 FETCH and OFFSET we are able to follow the ISO SQL 2008 standard for our limiting.

http://sqlfiddle.com/#!6/11a33/3

Page Size option

DECLARE @PageSize INT = 5

SELECT *
  FROM trans
  ORDER BY transactionTime DESC
  OFFSET 0 ROWS
  FETCH NEXT (SELECT @PageSize) ROWS ONLY
;

In SQL Server 2012, the new FETCH on the ORDER BY clause allows for queries to obtain the number of rows to retrieve!

http://sqlfiddle.com/#!6/11a33/6

Using ROW_NUMBER

SELECT id, transactionId, transactionTime, amount
  FROM (
    SELECT
      ROW_NUMBER() OVER(ORDER BY transactionTime DESC) AS row
     ,*
      FROM trans
  ) AS r
  WHERE r.row <= 5
;

Using the ROW_NUMBER() windowing function, we can create a poor man's FETCH and OFFSET by filtering the row numbers returned to be the number of records we want to obtain.

This idea came from Suprotim Agarwal's blog post on SQL Server Curry.

http://sqlfiddle.com/#!6/11a33/7

Performance

"Inform the database whenever you don't need all rows."

-- Markus Winand, Use the Index Luke - Top-N Rows Queries

SQL Server offers many different ways to get the last 5 records, but one must think of performance.  If you have an indexed order by then the DBMS would not even have to perform the sort!

Living with Function Programming OR Unit Testing F# with FsUnit

"Let there be some more test made of my metal
Before so noble and so great a figure"
-- Shakespeare, Measure for Measure
Act I, Scene I, Lines 48-49

The Quest

One of the first things I asked myself while learning F# was, "This is great, how do I test this stuff?"  I did what any programmer would do and asked the Google, which pointed me to a question on Stackoverflow and a slide deck on slideshare.  Between the the top answer on Stackoverflow and the 5th slide in the deck.  I was off and running doing TDD in F# with FsUnit and NCrunch!

Intro to FsUnit

FsUnit uses the BDD should style of testing, which I personally am starting to prefer.  I hear, an example would be nice right about now; in the back of my head, here you go (these examples are taken from the FsUnit README.md):

1 |> should equal 1
1 |> should not' (equal 2)

"ships" |> should startWith "sh"
"ships" |> should not' (startWith "ss")
"ships" |> should endWith "ps"
"ships" |> should not' (endWith "ss")

[1] |> should contain 1
[] |> should not' (contain 1)

The pattern which FsUnit follows is:

{actual-result} |> should {statement-to-test} {expected-result}

Looking at the first line in the example (1 |> should equal 1) we have:

1 as the actual-result
equal as the statement-to-test
1 as the expected-result

F# is a functional language, so we should think of everything in the FsUnit test as a function.  Given that, we see that the should is looking for: an actual-result, a statement-to-test (equal, not', endWith, contain, ...), and the expected-result.  Using the |> pipe we get a readable form like, 1 should equal 1 (1 |> should equal ).

FsUnit with the Even or Odd example

Let us look at MSDN's favorite F# example, even or odd; using FsUnit?

Say we have the following:

let isOdd x =
  if x % 2 = 0 then false
  else true
  
let isEven x = isOdd x |> not

We can use the following to test these two functions using FsUnit and NUnit.

[<Test>]
let ``Given 2 isOdd is false`` () =
  isOdd 2 |> should be False

[<Test>]
let ``Given 2 isEven is true`` () =
   isEven 2 |> should be True

[<Test>]
let given_3_isOdd_is_true () =
  isOdd 3 |> should equal true

[<Test>]
let given_3_isEven_is_false () =
  isEven 3 |> should equal false

Looking at the tests we see that we can use backticks to allow for more readable names to our tests.  For boolean results we can also choose between should be False and should equal false, the first using FsUnit's keyword be with its False and the other using equal and F#'s false.  Both from my experience work the same, so it is more a question around readability and maintainability.

FsUnit FizzBuzz example

Using NUnit we can uses attributes like TestCase to pass in values to our FsUnit tests.  Again the voice in the back of my head is saying, "How about an example?", so here it is for FizzBuzz:

open NUnit.Framework
open FsUnit

 let (|DivisibleBy|_|) divisor n =
  if n % divisor = 0 then Some() else None

let fizzbuzz n =
  match n with
  | DivisibleBy 3 & DivisibleBy 5 -> "FizzBuzz"
  | DivisibleBy 3 -> "Fizz"
  | DivisibleBy 5 -> "Buzz"
  | _ -> string n

[<TestCase(2,"2")>]
[<TestCase(4,"4")>]
[<TestCase(3,"Fizz")>]
[<TestCase(9,"Fizz")>]
[<TestCase(5,"Buzz")>]
[<TestCase(25,"Buzz")>]
[<TestCase(15,"FizzBuzz")>]
[<TestCase(30,"FizzBuzz")>]
let ``Given this value the result is this`` (value, result) =
  fizzbuzz value |> should equal result

Using a parameterized active pattern (based on yet another Stackoverflow response from Tomas Petricek!) we are able to have a FizzBuzz solution which does not test the mod 15 directly and is very expandable (in case the business wants a Woof for 7!).

We see that using the NUnit TestCase attribute we can simply have one FsUnit test which is passed in different input for the argument to fizzbuzz and the expected result!

FizzBuzz - Now in SQL Server

"Yet again? What do you here? Shall we give o'er and
drown? Have you a mind to sink?"
-- Shakespeare, The Tempest
Act I, Scene I, Lines 38-39

The great thing about the FizzBuzz kata is that it is very easy to understand, but has nearly an unlimited number of ways to implement it.  I am fan of using the right tool for the job, but I do enjoy stretching things to see what they can and cannot do with the hope of gaining a different point of view.  Having said that let us take a look at implementing the FizzBuzz kata in SQL using SQL Server 2012.

Overview of FizzBuzz

The goal of the FizzBuzz kata is to create a list of values which move through a filter with the following rules:

• If the number given is divisible by 3 then produce "Fizz"
• If the number given is divisible by 5 then produce "Buzz"
• If the number is not divisible by either 3 or 5 then produce the number given

FizzBuzz has one edge case:

• If the number given is divisible by both 3 and 5 then produce "FizzBuzz"

An example would be the following:

We see that:
2  => 2
4  => 4
3  => Fizz
9  => Fizz
5  => Buzz
25 => Buzz
15 => FizzBuzz
45 => FizzBuzz

How to Generate the Dataset in SQL Server

Given that we are going to use SQL Server, the first question is how do we generate a dataset to run through our FizzBuzz?  We could create a table with all of the values that we need, but that is not very interesting.  Instead we will use a Common Table Expression (CTE) to generate our data.

We'll use a Tally Table as outline by Vinay Pugalia's blog post.

DECLARE 
 @min AS INT = 1
,@max AS INT = 20

;WITH [range] AS (
  SELECT @min AS num
  UNION ALL
  SELECT num + 1 FROM [range] WHERE num < @max
)
SELECT * 
  FROM [range]
;

This will generate the number 1 to 20 in a dataset.

http://sqlfiddle.com/#!6/9cd0c/7

We can generate any dataset starting at @min up to @max using this CTE, not bad.

Setting up the Business Logic

With our Common Table Expression set up to generate whatever range of numbers we want, we can now focus on the Business Logic of the problem.  Since we are looking for particular values to produce a result in some cases it sounds like the perfect job for the CASE statement.

DECLARE 
 @min AS INT = 1
,@max AS INT = 20

;WITH [range] AS (
  SELECT @min AS num
  UNION ALL
  SELECT num + 1 FROM [range] WHERE num < @max
)
SELECT
  CASE
    WHEN num % 15 = 0 THEN 'FizzBuzz'
    WHEN num % 3  = 0 THEN 'Fizz'
    WHEN num % 5  = 0 THEN 'Buzz'
    ELSE                   CAST(num AS VARCHAR)
  END AS value
  FROM [range]
;

Gives use the output we would want:

http://sqlfiddle.com/#!6/9cd0c/12

This gets us what we want, but personally I do not like repeating the logic for divisible by 3 and 5 in the WHEN num % 15 = 0 case.

How about if we join to a set for the Fizz and another set for the Buzz?  If we could do that we could use the CONCAT string operation to cover the divisible by 15 edge case.

DECLARE 
 @min AS INT = 1
,@max AS INT = 20

;WITH [range] AS (
  SELECT @min AS num
  UNION ALL
  SELECT num + 1 FROM [range] WHERE num < @max
)
SELECT
  CASE
    WHEN CONCAT(fizz.value, buzz.value) <> '' 
      THEN CONCAT(fizz.value, buzz.value)
    ELSE CAST(num AS VARCHAR)
  END AS value
  FROM [range]
  LEFT OUTER JOIN (
    SELECT 
      num AS id
    ,'Fizz' AS value
    FROM [range] WHERE num % 3 = 0
  ) AS fizz
    ON fizz.id = [range].num
  LEFT OUTER JOIN (
    SELECT 
      num AS id
    ,'Buzz' AS value
    FROM [range] WHERE num % 5 = 0
  ) AS buzz
    ON buzz.id = [range].num
;

We create subselects which give us the Fizz and Buzz filters we are looking for.  We then use the CONCAT to join a Fizz to a Buzz which will give us FizzBuzz on numbers divisible by both 3 and 5.  If we do not have a Fizz or a Buzz the CONCAT will give us '' (empty string), which means we'll want to return the number as a string.

This gives us:

http://sqlfiddle.com/#!6/9cd0c/20

We can make this a bit more readable using two more CTEs.

DECLARE 
 @min AS INT = 1
,@max AS INT = 20

;WITH [range] AS (
  SELECT @min AS num
  UNION ALL
  SELECT num + 1 FROM [range] WHERE num < @max
)
, fizz AS (
  SELECT 
      num AS id
    ,'Fizz' AS value
    FROM [range] WHERE num % 3 = 0
)
, buzz AS (
  SELECT 
      num AS id
    ,'Buzz' AS value
    FROM [range] WHERE num % 5 = 0
)
SELECT
  CASE
    WHEN CONCAT(fizz.value, buzz.value) <> '' 
      THEN CONCAT(fizz.value, buzz.value)
    ELSE CAST(num AS VARCHAR)
  END AS value
  FROM [range]
  LEFT OUTER JOIN fizz
    ON fizz.id = [range].num
  LEFT OUTER JOIN buzz
    ON buzz.id = [range].num
;

Which still gives us the same output.

http://sqlfiddle.com/#!6/9cd0c/22

Personally I find the three CTEs a bit cleaner to read and understand, but I understand that not everyone will feel the same way.

There you have it FizzBuzz using SQL Server.  You can find the solution on SQL Fiddle (and see all my typing errors to get to it).

"They say there's no such place... as Paradise. Even if you search to the ends of the Earth, there's nothing there. No matter how far you walk, it's always the same road. It just goes on and on. But, in spite of that... Why am I so driven to find it? A voice calls to me... It says, 'Search for Paradise.'"
-- Keiko Nobumoto, Wolf's Rain
Final Lines