Select Distinct Values from 1 Column

How to select distinct value from one column only

Query:

SELECT `key`, MAX(`name`) as name
FROM `table`
GROUP BY `key`

DISTINCT for only one column

If you are using SQL Server 2005 or above use this:

SELECT *
  FROM (
                SELECT  ID, 
                        Email, 
                        ProductName, 
                        ProductModel,
                        ROW_NUMBER() OVER(PARTITION BY Email ORDER BY ID DESC) rn
                    FROM Products
              ) a
WHERE rn = 1

EDIT:
Example using a where clause:

SELECT *
  FROM (
                SELECT  ID, 
                        Email, 
                        ProductName, 
                        ProductModel,
                        ROW_NUMBER() OVER(PARTITION BY Email ORDER BY ID DESC) rn
                    FROM Products
                   WHERE ProductModel = 2
                     AND ProductName LIKE 'CYBER%'

              ) a
WHERE rn = 1

select only distinct value from one column

More recent versions of Sybase support row_number(). You would use it in a subquery like this:

SELECT RECEIPT_OFFICE_PREFIX, BANKING_NO, STATUS_CD
FROM (SELECT C.RECEIPT_OFFICE_PREFIX, B.BANKING_NO, B.STATUS_CD,
             ROW_NUMBER() OVER (PARTITION BY B.BANKING_NO ORDER BY B.BANKING_NO) as seqnum
      FROM TControl B JOIN
           TComponent C
           ON B.BANKING_NO = C.BANKING_NO AND B.COMPANY_ID = C.COMPANY_ID
      WHERE C.DEPOSIT_BANK_ACCT = 'xxx-xxxxxx-xxxxx' AND     
            B.RECEIPT_OFFICE_PREFIX = C.RECEIPT_OFFICE_PREFIX AND
            B.STATUS_CD != 'C'
    ) BC
WHERE seqnum = 1
ORDER BY BANKING_NO;

SELECT DISTINCT on one column

Assuming that you're on SQL Server 2005 or greater, you can use a CTE with ROW_NUMBER():

SELECT  *
FROM    (SELECT ID, SKU, Product,
                ROW_NUMBER() OVER (PARTITION BY PRODUCT ORDER BY ID) AS RowNumber
         FROM   MyTable
         WHERE  SKU LIKE 'FOO%') AS a
WHERE   a.RowNumber = 1

How to get Distinct value for a column on the basis of other column in Oracle

Do a GROUP BY to get distinct COL1 values.

Use COALESCE() to return the COL3 value if there exists a COL1 = COL2 row, otherwise return the max COL3 value for the COL1. (Could use MIN() too, if that's better.)

select COL1,
       COALESCE( MAX(CASE WHEN COL1 = COL2 THEN COL3 END), MAX(COL3) )
FROM table1
WHERE COL1 IS NOT NULL
GROUP BY COL1

ORACLE SELECT DISTINCT VALUE ONLY IN SOME COLUMNS

It seems you need to select one (random) row for each distinct combination of id and type. If so, you could do that efficiently using the row_number analytic function. Something like this:

select id, type, value, account
from   (
         select id, type, value, account, 
                row_number() over (partition by id, type order by null) as rn
         from   your_table
       )
where  rn = 1
;

order by null means random ordering of rows within each group (partition) by (id, type); this means that the ordering step, which is usually time-consuming, will be trivial in this case. Also, Oracle optimizes such queries (for the filter rn = 1).

Or, in versions 12.1 and higher, you can get the same with the match_recognize clause:

select id, type, value, account
from   my_table
match_recognize (
  partition by id, type
  all rows  per match
  pattern   (^r)
  define    r as null is null
);

This partitions the rows by id and type, it doesn't order them (which means random ordering), and selects just the "first" row from each partition. Note that some analytic functions, including row_number(), require an order by clause (even when we don't care about the ordering) - order by null is customary, but it can't be left out completely. By contrast, in match_recognize you can leave out the order by clause (the default is "random order"). On the other hand, you can't leave out the define clause, even if it imposes no conditions whatsoever. Why Oracle doesn't use a default for that clause too, only Oracle knows.

How to select distinct value from two columns into one column

here is one way:

select string_Agg(Location,',') as distinct_location_list
 from (
select A_LOC Location FROM tablename
union 
select Z_LOC Location FROM tablename
) tt

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)

---

Related Topics