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

I have a list of ids that have different number of instances when running a query. I want to figure out a way to limit those instances to just two for each id. For example:

If my initial query results are:

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

I want to modify the script so the query results look like:

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

CodePudding user response：

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!