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

    Unanswered: Doubt with a simple query

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

    I have the following data:


    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:


    Thank you!

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

    this shld work right ?

  3. #3
    Join Date
    Nov 2003
    Provided Answers: 23
    "Greatest-N-Per-Group" queries are usually solved using window functions:

    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.

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

    Tips for good questions:

Posting Permissions

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