How to get the first instance of a value partitioned by another column?

Question

I have a table and I am trying to get the first person in the table with gender = 'M' and the first person with gender = 'F'

First person in this case = ORDER BY name in alphabetical order

name | gender | .......other data
A        M
B        M
C        F
D        F
E             
F        M
G        F

How do I get a result table with the first instance of 'M' , 'F' without the null/empty column?

Ideal result:

name | gender | ........other data
A       M
C       F

Thanks for the help!

Answer 1

You can use row_number() function for that like this:

SELECT name,gender from (
    SELECT name,gender,
           row_number() OVER(PARTITION BY gender ORDER BY name ASC) as rnk
    FROM YourTable)
WHERE rnk = 1

You can add your other columns after the gender if you want.

Answer 2

SELECT name, gender
FROM your table
WHERE gender = "M"
ORDER BY NAME
Fetch first row only

Union all

SELECT name, gender
FROM your table
WHERE gender = "F"
ORDER BY NAME
Fetch first row only

Answer 3

One method in Postgres is to use distinct on:

select distinct on (gender) t.*
from t
order by gender, name;

This (conveniently) doesn't require listing all the genders in the query, can make use of an index on (gender,name), and is often faster than row_number(). Of course, the downside is that this is not standard, and only works in Postgres and Postgres-derived databases.