Get Rows Based on Distinct Values from One Column

Get rows based on distinct values from one column

Use drop_duplicates with specifying column COL2 for check duplicates:

df = df.drop_duplicates('COL2')
#same as
#df = df.drop_duplicates('COL2', keep='first')
print (df)
    COL1  COL2
0  a.com    22
1  b.com    45
2  c.com    34
4  f.com    56

You can also keep only last values:

df = df.drop_duplicates('COL2', keep='last')
print (df)
    COL1  COL2
2  c.com    34
4  f.com    56
5  g.com    22
6  h.com    45

Or remove all duplicates:

df = df.drop_duplicates('COL2', keep=False)
print (df)
    COL1  COL2
2  c.com    34
4  f.com    56

Select Unique Rows Based on Single Distinct Column - MySQL

If you are using MySQL 5.7 or earlier, then you may join your table to a subquery which finds the most recent record for each email:

SELECT t1.id, t1.title, t1.email, t1.commentname
FROM yourTable t1
INNER JOIN
(
    SELECT email, MAX(id) AS latest_id
    FROM yourTable
    GROUP BY email
) t2
    ON t1.email = t2.email AND t1.id = t2.latest_id;

If you are using MySQL 8+, then just use ROW_NUMBER here:

WITH cte AS (
    SELECT id, title, email, commentname,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id DESC) rn
    FROM yourTable
)

SELECT id, title, email, commentname
FROM cte
WHERE rn = 1;

Note: Your expected output probably has a problem, and the id = 6 record is the latest for rob@hotmail.com.

select unique rows based on single distinct column

Quick one in TSQL

SELECT a.*
FROM emails a
INNER JOIN 
  (SELECT email,
    MIN(id) as id
  FROM emails 
  GROUP BY email 
) AS b
  ON a.email = b.email 
  AND a.id = b.id;

Selecting rows based on distinct value of one column (per group) in Postgres

You can try something like this

select min(col1) as col1 , 
                    col2, 
                    col3, 
                    string_agg(col4, ',') 
               from table 
               where col2 like '%dexamethasone%' 
                        and col1 = 'Dexamethasone' 
               group by col2, col3;

try reading group by clause in postgres.

sql - select rows with a distinct value in a specific column

This is one way to do it using a derived table so you select one id per team and join it to the original table.

select t.id, t.player_name, t.team
from tablename t
join (select team, min(id) as minid from tablename group by team) x
on x.team = t.team and x.minid = t.id

How to select distinct based on condition (another column)

You can get the row number for each row partitoned by house and ordered by date desc. then only select the rows with row number = 1:

select house, people, date
from(select house, people, date, row_number() over(partition by house order by date desc) rn
from table_name) t
where rn = 1

Fiddle

---

Related Topics