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

    Unanswered: Doubt 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

    Thank you!

  2. #2
    Join Date
    Oct 2007
    Posts
    246
    select ename, max(date) from tbaname group by ename ;

    this shld work right ?

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    "Greatest-N-Per-Group" queries are usually solved using window functions:

    Code:
    select id, name, date
    from (
      select id, name, date,
               row_number() over (partition by name order by date desc) as rn
      from table1
    ) t
    where rn = 1
    order by id, name;
    The above is ANSI standard SQL, so it also works with other DBMS that support modern SQL

    Postgres also offers a proprietary way of doing this through the DISTINCT ON operator.

    Code:
    select distinct on (name) id, name, date
    from thable1
    order by name, date desc
    DISTINCT ON is usually faster then the solution using a window function.

    More details in the manual: https://www.postgresql.org/docs/curr...l#SQL-DISTINCT
    Last edited by shammat; 07-05-16 at 02:41.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

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