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 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;
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
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
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 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)
Select distinct for one column
For rows that are unique by colD, you will have to decide which other column values will be discarded. Here, within the over clause
I have use partition by
colD which provides the wanted uniqueness by that column, but the order by
is arbitrary and you may want to change it to suit your needs.
select
d.*
from (
select
t.*
, row_number() over (partition by t.colD
order by t.KeyPart1,t.KeyPart2,t.KeyPart) as rn
from yourtable t
) d
where d.rn = 1;
How to select distinct value from one column only
Query:
SELECT `key`, MAX(`name`) as name
FROM `table`
GROUP BY `key`
Related Topics
How to Convert Rows to Columns in Oracle
Postgresql Sequence Based on Another Column
What Are the Use Cases for Selecting Char Over Varchar in SQL
Calculate Business Hours Between Two Dates
Mysql: Transactions VS Locking Tables
How to Convert a SQL Server 2008 Datetimeoffset to a Datetime
Get Next Sequence Value from Database Using Hibernate
Selecting Random Rows in MySQL
Fastest Way to Determine If Record Exists
Why (And How) to Split Column Using Master..Spt_Values
Combine Two Columns and Add into One New Column
How to Check Which Locks Are Held on a Table
Custom Date/Time Formatting in SQL Server
How to Interpret Precision and Scale of a Number in a Database