Select DISTINCT column in a DISTINCT condition

select distinct multiple columns
select distinct on one column with multiple columns returned
sql select unique values from multiple columns
select distinct count
sql distinct vs unique
select distinct oracle
sql count(distinct multiple columns)
sql select distinct values and count of each

I want to select distinct values from 'num' when it's status is 'YES' only, not including 'NO' at once?

Table:

+--------+-----+--------+
| id     | num | status |
+--------+-----+--------+
| 364519 | AAA | YES    |
| 364522 | BBB | YES    |
| 364525 | AAA | NO     |
| 364527 | CCC | YES    |
| 364530 | DDD | YES    |
| 364532 | EEE | YES    |
| 364535 | FFF | YES    |
| 364537 | DDD | YES    |
| 364538 | DDD | NO     |
| 364539 | FFF | NO     |
+--------+-----+--------+

needed Result:

+--------+-----+--------+
| id     | num | status |
+--------+-----+--------+
| 364522 | BBB | YES    |
| 364527 | CCC | YES    |
| 364532 | EEE | YES    |
+--------+-----+--------+

You could use HAVING:

SELECT MIN(id) AS id, num,'yes' AS status
FROM tab
GROUP BY num
HAVING SUM(status='NO') = 0;

DBFiddle Demo

SQL: DISTINCT Clause, Example - Finding Unique Values in Multiple Columns. Next, let's look at how to use the SQL DISTINCT clause to remove duplicates from more than one field in a​  SELECT with DISTINCT on multiple columns and ORDER BY clause. You can use an order by clause in the select statement with distinct on multiple columns. Here is an example: SQL Code: SELECT DISTINCT agent_code,ord_amount FROM orders WHERE agent_code='A002' ORDER BY ord_amount; Output:

Just a couple of other approaches. (having seems the cleanest)

Divide the data into two sets and make sure the "A" set has no data in the "B" set First approach using a outer join only return records in "A" set where no related num is found in "B" set.

SELECT Min(A.ID) as ID, A.num, A.status
FROM table A
LEFT JOIN table B
  on A.Num = B.Num
 and B.Status = 'NO'
WHERE A.Status = 'YES'
  and B.Num is null
GROUP BY A.Num, A.Status

Second approach using a not exists. similar only num records in "A" set not in "B" set.

SELECT min(A.ID) as ID, A.Num, A.Status
FROM table A
WHERE NOT EXISTS (SELECT 1 
                  FROM Table B
                  WHERE A.Num = B.Num
                    and B.Status = 'NO')
GROUP BY A.Num, A.Status

SQL Server SELECT DISTINCT, Sometimes, you may want to get only distinct values in a specified column of a table. To do this, you use the SELECT DISTINCT clause as follows: SELECT DISTINCT column_name FROM table_name; The query returns only distinct values in the specified column. The SQL SELECT DISTINCT Statement. The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values. SELECT DISTINCT column1, column2,

SELECT * FROM table
WHERE status='YES' AND num NOT IN (SELECT num FROM table WHERE status='NO')

Or this

SELECT * FROM table t
WHERE t.status='YES' AND NOT EXISTS (SELECT * FROM table WHERE status='NO' AND num=t.num)

SQLite SELECT DISTINCT, First, the DISTINCT clause must appear immediately after the SELECT keyword. Second, you place a column or a list of columns after the DISTINCT keyword. If  The SQL SELECT DISTINCT Statement retrieves unique records (by removing the duplicates) from the specified column in the SELECT Statement . The syntax of the SQL Server SELECT DISTINCT is: Columns: It allows us to pick the number of columns from the tables. It may be one or more. Source: One or more tables in the Database.

select distinct(num),id,status from table where status='YES'

Oracle SELECT DISTINCT By Practical Examples, The DISTINCT clause is used in a SELECT statement to filter duplicate rows in the result set. It ensures that rows returned are unique for the column or columns​  pandas documentation: Select distinct rows across dataframe. But Series.unique() works only for a single column. To simulate the select unique col_1,

I would just use group by cluase :

select min(id) as id, num, 'YES' as status
from table t
group by num
having min(status) = max(status) and min(status) = 'YES'; 

PostgreSQL SELECT DISTINCT By Practical Examples, In this statement, the values in the column_1 column are used to evaluate the duplicate. If you specify multiple columns, the DISTINCT clause will evaluate the​  Using the DISTINCT clause with the SELECT statement is the simple method. You just need to put the DISTINCT clause after the SELECT statement. Then after you have to specify the column name from which you want to fetch only the distinct values and not the duplicate values.

select distinct, MySQL DISTINCT with multiple columns. You can use the DISTINCT clause with more than one column. In this case, MySQL uses the combination of values in  Table.Distinct. 10/17/2019; 2 minutes to read; In this article Syntax Table.Distinct(table as table, optional equationCriteria as any) as table About. Removes duplicate rows from the table table. An optional parameter, equationCriteria, specifies which columns of the table are tested for duplication. If equationCriteria is not specified, all columns are tested.

DISTINCT - Caché & Ensemble 2018.1.3, DISTINCT - A SELECT clause that specifies to return only distinct values. If the column specified in the DISTINCT clause has rows that are NULL (contain no  The DISTINCT clause is used in a SELECT statement to filter duplicate rows in the result set. It ensures that rows returned are unique for the column or columns specified in the SELECT clause. The following illustrates the syntax of the SELECT DISTINCT statement: SELECT DISTINCT column_1 FROM table;

DISTINCT (column) function (DAX), DISTINCT (column). 12/10/2018; 2 minutes to read. In this article. Syntax; Return value; Remarks; Related functions; Example; See also. Returns a one-column  SQL COUNT() with DISTINCT: SQL COUNT() function with DISTINCT clause eliminates the repetitive appearance of a same data. The DISTINCT can comes only once in a given select statement.

Comments
  • What ID is it you are showing in your result? Say, if one num has multiple 'YES' and no 'NO', which ID would you want to show?
  • Not sure why, but this one makes my skin crawl