Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    2

    Unanswered: Select 4 most recent results

    Hi All, I am creating a database in SQL2K for collating test results taken at regular intervals across several different sites and frequencies. The table will look something like this:

    Site Date Data
    A 01/01/2013
    B 02/01/2013
    C 03/01/2013
    A 04/01/2013
    B 05/01/2013
    C 06/01/2013
    And so on...

    In total there will be about 300 sites, with up to 12 records per site every year. I want to be able to create a view showing the 4 most recent results for each site. Is there a simple way of doing this? Obviously getting the most recent result for each site is quite straight forward, but I can't work out how to get the last four. Unfortunately this is on a 2k box, although I could (at a push) use a 2k8 box if needed.

    Thanks in advance for any help.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Getting to SQL 2K8 at least would give you access to the window functions and CTEs, which make this nearly trivial. These features are in the Express version, so you may as well go right to SQL 2012.

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Trick about the month names.

    Please learn the ISO-8601 date display format; it is the only one allowed in ANSI/ISO Standard SQL. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[01][0-9]-00'


    CREATE TABLE Foobar
    (site_name CHAR(1) NOT NULL,
    reading_month CHAR(9) '[12][0-9][0-9][0-9]-[01][0-9]-00' NOT NULL,
    PRIMARY KEY (site_name, reading_month),
    foo_reading INTEGER NOT NULL);

    SELECT X.*
    FROM (SELECT site_name, reading_month, foo_reading,
    ROW_NUMBER()
    OVER (PARTITION BY site_name
    ORDER BY reading_month DESC)
    AS reading_seq
    FROM Foobar) AS X
    WHERE reading <= 4;

    You do need to get to SQL Server 2012 as fast as you can; too much good stuff in it.

  4. #4
    Join Date
    Mar 2012
    Location
    Somewhere In Europe
    Posts
    24
    Hi Stanni

    Please try this:

    Code:
    SELECT t.site, t.Date 
    FROM your_table AS t 
     WHERE t.Date IN (SELECT TOP(4) t1.Date FROM your_table AS t1
                              WHERE  t1.site = t.site ORDER BY t1.Date DESC)

    Regards
    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •