Distinct in SQL Server

SQL Server TOP(1) with distinct

If you're only getting the TOP 1 then distinct is irrelevant. It's also irrelevant since grouping by the column will give you distinct values,

However, If you want more than one just remove the parentheses:

SELECT DISTINCT TOP(2) 
    i_version_id
FROM 
    [PaymentGateway_2006].[dbo].[merchant] 
WHERE 
    dt_updated_datetime > '2013-11-11'
GROUP BY
    i_version_id 
ORDER BY 
    i_version_id;

How to select distinct rows from SQL Server table

With the example data given, this will work:

SELECT col_1, col_2, col_3, col_4, name 
FROM table_name 
WHERE (user = 'user_1' OR user = 'GLOBAL') 
  AND name = 'one_four_eleven' 
  AND sub_name != 'ALL' 
GROUP BY col_1, col_2, col_3, col_4, name

or this

SELECT distinct col_1, col_2, col_3, col_4, name 
FROM table_name 
WHERE (user = 'user_1' OR user = 'GLOBAL') 
  AND name = 'one_four_eleven' 
  AND sub_name != 'ALL' 

oh, and if you are wondering mysql was basically this:

SELECT max(col_1), max(col_2), max(col_3), max(col_4), max(name) 
FROM table_name 
WHERE (user = 'user_1' OR user = 'GLOBAL') 
  AND name = 'one_four_eleven' 
  AND sub_name != 'ALL' 
GROUP BY sub_name

but that is not as good as the first two.

Using DISTINCT in SQL Query

You can either distinct by all columns selected :

SELECT DISTINCT 
    Suppliers.SupplierID, Customers.CompanyName, Products.ProductName,    
    Orders.OrderDate
FROM         
    Suppliers INNER JOIN
    Products ON Suppliers.SupplierID = Products.SupplierID CROSS JOIN
    Customers INNER JOIN
    Orders ON Customers.CustomerID = Orders.CustomerID
WHERE 
    Orders.OrderDate <='1/1/1999'
ORDER BY     
    Suppliers.SupplierID

or use group by instead if you need to distinct only by SupplierID. DISTINCT is not a function, hence DISTINCT(Suppliers.SupplierID) means the same as simply put DISTINCT word after SELECT in this case (see the 2nd reference below).

For Reference :

• http://blog.sqlauthority.com/2007/12/20/sql-server-distinct-keyword-usage-and-common-discussion/
• http://weblogs.sqlteam.com/jeffs/archive/2007/10/12/sql-distinct-group-by.aspx

sql server select distinct with ID

Use CTE.

Query

;with cte as
(
    select rn = row_number() over
    (
        partition by cola,colb,colc
        order by id
    ),*
    from Table1
)
insert into Table2(id,cola,colb,colc)
select id,cola,colb,colc
from cte
where rn = 1;

SQL Fiddle

Using DISTINCT along with GROUP BY in SQL Server

Use DISTINCT to remove duplicate GROUPING SETS from the GROUP BY clause

In a completely silly example using GROUPING SETS() in general (or the special grouping sets ROLLUP() or CUBE() in particular), you could use DISTINCT in order to remove the duplicate values produced by the grouping sets again:

SELECT DISTINCT actors
FROM (VALUES('a'), ('a'), ('b'), ('b')) t(actors)
GROUP BY CUBE(actors, actors)

With DISTINCT:

actors
------
NULL
a
b

Without DISTINCT:

actors
------
a
b
NULL
a
b
a
b

But why, apart from making an academic point, would you do that?

Use DISTINCT to find unique aggregate function values

In a less far-fetched example, you might be interested in the DISTINCT aggregated values, such as, how many different duplicate numbers of actors are there?

SELECT DISTINCT COUNT(*)
FROM (VALUES('a'), ('a'), ('b'), ('b')) t(actors)
GROUP BY actors

Answer:

count
-----
2

Use DISTINCT to remove duplicates with more than one GROUP BY column

Another case, of course, is this one:

SELECT DISTINCT actors, COUNT(*)
FROM (VALUES('a', 1), ('a', 1), ('b', 1), ('b', 2)) t(actors, id)
GROUP BY actors, id

With DISTINCT:

actors  count
-------------
a       2
b       1

Without DISTINCT:

actors  count
-------------
a       2
b       1
b       1

For more details, I've written some blog posts, e.g. about GROUPING SETS and how they influence the GROUP BY operation, or about the logical order of SQL operations (as opposed to the lexical order of operations).

Select the field as Distinct having data type as Text. Sql Server

You can use:

SELECT DISTINCT CONVERT(varchar(max), text_column) ...

Or for less memory usage, if you're happy with the first x bytes (say, 900):

SELECT DISTINCT LEFT(text_column, 900) ...

While the cast/convert answers work, and while it's questionable to want to perform a distinct operation on data this large in the first place, the real fix is to stop using the TEXT data type. It has been deprecated since 2005. You should be using VARCHAR(MAX) instead, for a whole variety of reasons.

How to Select Every Row Where Column Value is NOT Distinct

This is significantly faster than the EXISTS way:

SELECT [EmailAddress], [CustomerName] FROM [Customers] WHERE [EmailAddress] IN
  (SELECT [EmailAddress] FROM [Customers] GROUP BY [EmailAddress] HAVING COUNT(*) > 1)

SQL to return distinct values then filter by date

So you want the IPs that appear on 2019-03-25, and don't appear before that:

SELECT IP FROM tblIPs WHERE DATE = '2019-03-25'
EXCEPT
SELECT IP FROM tblIPs WHERE DATE < '2019-03-25'
;

If you also want to exclude IPs that appear again after 2019-03-25, change the < to <>

Select distinct values across columns

One option is to UNPIVOT your data and then PIVOT via a dense_rank()

Example

Declare @YourTable Table ([ID] varchar(50),[Color1] varchar(50),[Color2] varchar(50),[Color3] varchar(50),[Color4] varchar(50))
Insert Into @YourTable Values 
  (1,'Red','Blue','Blue','Green')

Select *
 From  (
        Select A.ID
              ,B.Item
              ,Col = concat('Color',dense_rank() over (partition by ID order by Item) )
         from @YourTable A
         Cross Apply ( values (color1)
                             ,(color2)
                             ,(color3)
                             ,(color4)
                     ) B(Item)
         Where ID=1
       ) src
 Pivot (max(item) for Col in (Color1,Color2,Color3,Color4) ) pvt

Returns

ID  Color1  Color2  Color3  Color4
1   Blue    Green   Red     NULL

Note: The Where ID=1 is optional

---

Related Topics