Selecting Unique Values from a Column

How to select unique records by SQL

With the distinct keyword with single and multiple column names, you get distinct records:

SELECT DISTINCT column 1, column 2, ...
FROM table_name;

How do I select unique values from multiple columns (each value being unique not the total row)

I would do a union of queries to get all distinct telephones by column then do a query on this union of telephones to get them only once:

SELECT DISTINCT `Tel` FROM
( 
  SELECT DISTINCT `Tel1` AS `Tel` FROM products
  UNION
  SELECT DISTINCT `Tel2` AS `Tel` FROM products
  UNION
  SELECT DISTINCT `Tel3` AS `Tel` FROM products
  UNION
  SELECT DISTINCT `Tel4` AS `Tel` FROM products
) all_telephones

Pandas select unique values from column

You need to select the column correctly then apply unique

csvData['ip'].unique().tolist()
Out[677]: ['111.111.111.111', '222.222.222.222']

SQL - Selecting unique values from one column then filtering based on another

One way via a list of IDs appearing once:

select * from T where Product_type = 'B' and id in (
select id from T
    group by id
    having count(id) = 1)

selecting unique values from a column

Use the DISTINCT operator in MySQL:

SELECT DISTINCT(Date) AS Date FROM buy ORDER BY Date DESC;

Select unique values in a column

Just use distinct. It will return distinct rows on the basis of columns you're selecting. That is, duplicate rows will be removed.

select distinct s2.inv_mast_uid as inv_mast_uid,
   s2.item_id,
   count_of_duplicates,
   s1.supplier_part_no,
   s1.supplier_id as supplier_id,
   s1.extended_desc,
   s1.item_desc
from   s2
   join s1
     on s1.supplier_part_no = s2.supplier_part_no
where  s1.supplier_id = s2.supplier_id
   and s1.item_desc = s2.item_desc
   and ( s1.extended_desc = s2.extended_desc
          or (s1.extended_desc is null
             and s2.extended_desc is null) )

I guess you've put the wrong parenthesis in the last line, so added that there in the or condition.

BUT, if you're looking for distinct value of only the column inv_mast_uid and do not bother about what values on other columns, you can do this:

Put the last select as s3, then write the select as follows:

select p.* 
    from s3 as p
inner join (select inv_mast_uid, min(supplier_id) from
s3 group by s2.inv_mast_uid) as q
on p.inv_mast_uid= q.inv_mast_uid
    and p.supplier_id = q.supplier_id

Selecting distinct values of a column with specific values of other columns

We can use a condition with if/else after the group_by

library(dplyr)
df1 %>%
   group_by(ID) %>% 
   summarise(across(everything(), ~ if(all(. == 0)) 0 
       else unique(.[. !=0])), .groups = 'drop')

-output

# A tibble: 4 x 5
#     ID     X     Y     R     Z
#  <int> <dbl> <dbl> <dbl> <dbl>
#1     1     0     2     0     1
#2     2     1     0     0     1
#3     3     1     1     1     1
#4     4     0     1     1     0

data

df1 <- structure(list(ID = c(1L, 1L, 2L, 3L, 3L, 4L, 4L), X = c(0L, 
0L, 1L, 1L, 1L, 0L, 0L), Y = c(2L, 2L, 0L, 1L, 1L, 0L, 1L), R = c(0L, 
0L, 0L, 0L, 1L, 1L, 1L), Z = c(1L, 0L, 1L, 1L, 1L, 0L, 0L)),
class = "data.frame", row.names = c(NA, 
-7L))

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

MySQL: SELECT UNIQUE VALUE

Try to use DISTINCT like this:

SELECT DISTINCT mycolumn FROM mytable

EDIT:

Try

select mycolumn, count(mycolumn) c from mytable
group by mycolumn having c = 1

---

Related Topics