Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2016
    Posts
    4

    Unanswered: Help with a simple query

    I have the table table1 with the columns id, name, date;

    I have the following data:

    1,paul,01/01/2016
    2,ana,01/01/2016
    3,ana,20/06/2016

    How can i write a select query that brings me back, when there is the same name with more than one date, only the record with the most recent date? Plus the records with only one date.
    The result i want in my example would be:

    1,paul,01/01/2016
    3,ana,20/06/2016

    Thanks.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,794
    Provided Answers: 11
    Use a CTE
    Code:
    with ThisThingHere (id, name, date, recno)
    as
    (select id, name, date, row_number() over (partition by name order by date desc)
     from table)
    select *
    from ThisThingHere
    where recno = 1
    Not tested code, so you may have to fix a few syntax errors along the way.

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

    Two ways to do it ..

    I have the table table1 with the columns id, name, date
    You need to get in the habit of posting DDL. This is basic netiquette for the last 30 years on SQL forums. Next you need to read a book on basic data modeling, so you will know about ISO 11179 naming rules. Here is a re-write; get a better data element name than "foo_"

    CREATE TABLE Foobar
    (foo_id CHAR(5) NOT NULL PRIMARY KEY, --- keys are requreid!
    foo_name VARCHAR(10) NOT NULL,
    foo_date DATE NOT NULL);

    A table must have a key, by definition. Things like "_name" and "_date" are called attribute properties. Think of them as adjectives; they need a noun (attribute) to be used correctly. There is also the problem that date is a reserved word in SQL.

    T look it is going. You have obviously been doing this for a few weeks. This is only a few weeks here is only one display format allowed in ANSI/ISO standard SQL. Another part of netiquette is to always post insertion statements as part of the DDL. This lets people build your sample data and test their attempts.

    INSERT INTO Foobar
    VALUES
    ('0001', 'paul', '2016-01-01'),
    ('0002', 'ana', '2016-01-01'),
    ('0003', 'ana', '2016-06-20');

    How can i write a select query that brings me back, when there is the same name with more than one date, only the record with the most recent date? Plus the records [sic] with only one date.
    There are several ways to do this; you can use the max () or row_number () in a CTE:

    WITH X
    AS
    (SELECT foo_id, foo_name, foo_date,
    ROW_NUMBER() OVER (PARTITION BY foo_name
    ORDER BY foo_date DESC)
    AS recent_date_seq
    FROM Foobar)
    SELECT *
    FROM X
    WHERE recent_date_seq = 1;

    The other version looks like this:

    WITH X
    AS
    (SELECT foo_id, foo_name, foo_date,
    MAX(foo_date) OVER (PARTITION BY foo_name)
    AS recent_date
    FROM Foobar)
    SELECT *
    FROM X
    WHERE recent_date = foo_date;

    I prefer the version with MAX(). While I have not tested it, MAX() will require no sorting and could take advantage of some indexing.

    It is any consolation. My rule of thumb is it takes at least a year of full-time employment to get to the point that you can write bad SQL. Think of it is trying to learn Chinese or Russian when you come from a Romance language background.

Posting Permissions

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