SQL - Select Distinct Only on One Column

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

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

SQL: How to apply distinct only for one column out of many?

OrderID will return more than one record when a customer has more than one order.

DISTINCT works on whole records, not individual fields so if there's more than one order for a customer it will show both records - the customer will be the same, but the order id is different so both records will be distinct.

If you COUNT the OrderID and Group the CustomerName you'll get unique customers and how many orders they've made:

SELECT CustomerName, COUNT(OrderID) AS Count_Of_Orders
FROM Customers LEFT JOIN Orders ON Orders.CustomerID = Customers.CustomerID
GROUP BY CustomerName
ORDER BY CustomerName  

SQL - select distinct only on one column

Since you don't care, I chose the max ID for each number.

select tbl.* from tbl
inner join (
select max(id) as maxID, number from tbl group by number) maxID
on maxID.maxID = tbl.id

---

Query Explanation

 select 
    tbl.*  -- give me all the data from the base table (tbl) 
 from 
    tbl    
    inner join (  -- only return rows in tbl which match this subquery
        select 
            max(id) as maxID -- MAX (ie distinct) ID per GROUP BY below
        from 
            tbl 
        group by 
            NUMBER            -- how to group rows for the MAX aggregation
    ) maxID
        on maxID.maxID = tbl.id -- join condition ie only return rows in tbl 
                                -- whose ID is also a MAX ID for a given NUMBER

SQL Query Multiple Columns Using Distinct on One Column Only

select * from tblFruit where
tblFruit_ID in (Select max(tblFruit_ID) FROM tblFruit group by tblFruit_FruitType)

SQL - Select distinct only one column

GROUP BY urs.username with MAX(urs.username) will give you distinct values for username:

SELECT ur.user_by, MAX(urs.username) AS username
FROM 
      ( SELECT *
        FROM users 
        LIMIT 10
     ) AS ur
  LEFT JOIN 
      people AS urs 
    ON 
      ur.user_by = urs.username 
 GROUP BY  ur.user_by
 LIMIT 10;

In order to get distinct values, you have to use GROUP BY with an aggregate function to select only one row for each group. In your case MAX will work find with username and only select one value for each group of user_by.

---

Update:

To select only 10 unique users from the users table, you use DISTINCT user_by with LIMIT 10 inside the subquery itself, like this:

SELECT ur.user_by, MAX(urs.username) AS username
FROM 
(
        SELECT DISTINCT user_by
        FROM users 
        LIMIT 10
 ) AS ur
  LEFT JOIN 
      people AS urs 
    ON 
      ur.user_by = urs.username 
 GROUP BY  ur.user_by
 LIMIT 10;

Select Distinct on one column, without ordering by that column

The general answer to your question is that when using DISTINCT ON (x, ...) in SELECT statement in postgresql, the database sorts by the values in the distinct clause in order to make it easy to tell if the rows have distinct values (once they're ordered by the values, it only takes one pass for the db to remove duplicates, and it only needs to compare adjacent rows. Because of this, the db forces you to sort by the same columns in the distinct clause.

You can work around this by making your original query a subquery, like so:

SELECT t.id FROM
  (SELECT DISTINCT ON (countries.id) countries.id
    , province_infos.population
    , country_infos.founding_date
   FROM countries
   ...
   ORDER BY countries.id, province_infos.population DESC, country_infos.founding_date  ASC 
  )t
ORDER BY t.population DESC, T.founding_date ASC

SQL Query - Distinct on One Column for Distinct Value of Other (with INNER JOIN)

If you don't care which IP address you keep for each user_id / enum combo, then something like this should do:

SELECT user_id, min(client_ip_address), language_enum_code
FROM vw_user_session_log AS usl
INNER JOIN vw_user_topic_ownership AS uto
ON usl.user_id = uto.user_id
where client_ip_address is not null
group by user_id, language_enum_code

mysql using DISTINCT for only one column

For MySQL only (and related distributions, like MariaDB), you can use GROUP BY:

SELECT id, name, countryCode FROM tablename GROUP BY name

Please note that the row to be omitted is not influenced by ORDER BY or similar statements.

This is a MySQL behavior only, traditional handling of GROUP BY states that it should only be used together with aggregated values (see MySQL Handling of GROUP BY).

How to select distinct value from one column only

Query:

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

---

Related Topics