Is there a way to display the first two results of each unique id?

Question

I work in healthcare. In a Postgres database, we have a table member IDs and dates. I'm trying to pull the latest two dates for each member ID.

Simplified sample data:

A 1
B 1
B 2
C 1
C 5
C 7
D 1
D 2
D 3
D 4

Desired result:

A 1
B 1
B 2
C 1
C 5
D 1
D 2

Answer 1

• I get a strong feeling this is for a homework assignment and would recommend that you look into partitioning and specifically rank() function by yourself first before looking at my solution.
• Moreover, you have not specified how you received the initial result you provided, so I'll have to assume you just did select letter_column, number_column from my_table; to achieve the result.

So, what you actually want here is partition the initial query result into groups by the letter_column and select the first two rows in each. rank() function lets you assign each row a number, counting within groups:

select letter_column,
   number_column,
   rank() over (partition by letter_column order by number_column) as rank
from my_table;

Since it's a function, you can't use it in a predicate in the same query, so you'll have to build another query around this one, this time filtering the results where rank is over 2:

with ranked_results as (select letter_column,
                           number_column,
                           rank() over (partition by letter_column order by number_column asc) as rank
                    from my_table mt)
select letter_column,
       number_column
from ranked_results
where rank < 3;

Here's an SQLFiddle to play around: http://sqlfiddle.com/#!15/e90744/1/0

Hope this helps!