How to Select Unique Records by Sql

How to select unique records by SQL

With the distinct keyword with single and multiple column names, you get distinct records:

SELECT DISTINCT column 1, column 2, ...
FROM table_name;

Only select unique record for a condition in sql

You could use HAVING:

SELECT    USER_ID
FROM      USER_ACCESS 
GROUP  BY USER_ID
HAVING    MIN(ACCESS_COLUMN_ID) = 1
AND       MAX(ACCESS_COLUMN_ID) = 1

This query will get all user_id, but only unique ones because of the group by clause. Then it will take the minimum and maximum access_column_id it finds for each of them, and if these two values are both 1, then the user_id is retained in the final result set.

The above will have good performance, as it references the table only once.

For your interest, there are several other ways to get the same result. However they all need the table to be referenced twice. You might want to compare their readability and performance yourself:

NOT EXISTS

SELECT    DISTINCT USER_ID
FROM      USER_ACCESS UA1
WHERE     UA1.ACCESS_COLUMN_ID = 1
AND       NOT EXISTS (
              SELECT 1
              FROM   USER_ACCESS UA2
              WHERE  UA1.USER_ID = UA2.USER_ID
              AND    UA2.ACCESS_COLUMN_ID <> 1)

NOT IN

This is very similar to the previous one, but in my experience has not as good performance:

SELECT    DISTINCT USER_ID
FROM      USER_ACCESS
WHERE     ACCESS_COLUMN_ID = 1
AND       USER_ID NOT IN (
              SELECT USER_ID
              FROM   USER_ACCESS
              WHERE  ACCESS_COLUMN_ID <> 1)

Outer Self-Join

This often has better performance than the previous two solutions:

SELECT    DISTINCT USER_ID
FROM      USER_ACCESS UA1
LEFT JOIN USER_ACCESS UA2
       ON UA1.USER_ID = UA2.USER_ID
      AND UA2.ACCESS_COLUMN_ID <> 1
WHERE     UA1.ACCESS_COLUMN_ID = 1
AND       UA2.USER_ID IS NULL

The last NULL condition checks that the outer join did not yield any match (with ACCESS_COMUN_ID <> 1).

EXCEPT

This is syntax specific to SQL Server, but is easy to understand (Oracle has the similar MINUS);

SELECT    DISTINCT USER_ID
FROM      USER_ACCESS
WHERE     ACCESS_COLUMN_ID = 1
EXCEPT
SELECT    USER_ID
FROM      USER_ACCESS
WHERE     ACCESS_COLUMN_ID <> 1

Remark on DISTINCT

The DISTINCT keyword is easy to understand, but one might often get better performance by using a GROUP BY clause instead. This can be applied to all solutions mentioned above.

If it is certain that there cannot be two records with the same values for USER_ID and ACCESS_COLUMN_ID then the DISTINCT keyword can be left out in the above queries.

Select distinct rows from a query

Try distinct keyword

select 
DISTINCT -- this keyword
flujo, FECHA from El_Bosque 
where fecha between CONVERT(DATETIME,'2016-06-22 10:00:00',102) 
and CONVERT(DATETIME,'2016-06-28 00:00:00',102) and ( flujo >=0 ) 
order by fecha asc

Also note that your query uses CONVERT function, you could be better off by writing like

DECLARE @t1 datetime
DECLARE @t2 datetime
SELECT @t1=CONVERT(DATETIME,'2016-06-22 10:00:00',102) , @t2=CONVERT(DATETIME,'2016-06-28 10:00:00',102) 
select 
DISTINCT -- this keyword
flujo, FECHA from El_Bosque 
where fecha between @t1 and @t2 and ( flujo >=0 ) 
order by fecha asc

How to select unique records when duplicates exist

Here is the solution. This works

SELECT UNIQUE APEL_ID, MAX(TO_DATE(HEAR_DT, 'DD-MON-YY')) AS LATEST_HEAR_DT 
FROM mytable  
GROUP BY APEL_ID

Select unique records and insert into another table avoiding duplicates in it

SQL DEMO

You can use GROUP BY and EXISTS in sql server like below :

insert into userto (username, date)
select distinct username, date from userfrom uf
where 
not exists(select 1 from userto where username=uf.username and date=uf.date)

Select unique record from specific dates excluding the duplicate of it in previous dates

The date range looks wrong to me. The date 2018-06-30 is present in both groups. Keeping this in mind, here is NOT EXISTS solution:

SELECT UC_NAME
FROM t
WHERE DATE_FORMATION >= '2018-07-01'
AND   DATE_FORMATION <  '2019-07-01' -- made exclusive
AND   NOT EXISTS (
    SELECT 1
    FROM t AS x
    WHERE UC_NAME = t.UC_NAME
    AND DATE_FORMATION < '2018-07-01' -- made exclusive
)
GROUP BY UC_Names

You can also use aggregation:

SELECT UC_NAME
FROM t
WHERE  DATE_FORMATION      <  '2019-07-01'
GROUP BY UC_NAME
HAVING MIN(DATE_FORMATION) >= '2018-07-01'

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)

How to select unique records from a 5 column table where the values can be the same but in different orders

Replace all =! with <:

Select t.col1, t1.col1, t2.col1, t3.col1, t4.col1
from table1 t 
CROSS JOIN table1 t1
CROSS JOIN table1 t2
CROSS JOIN table1 t3
CROSS JOIN table1 t4
WHERE
    t.col1 < t1.col1 and t.col1 < t2.col1 and t.col1 < t3.col1 and t.col1 < t4.col1 
    and t1.col1 < t2.col1 and t1.col1 < t3.col1 and t1.col1 < t4.col1 
    and t2.col1 < t3.col1 and t2.col1 < t4.col1
    and t3.col1 < t4.col1

The problem with =! is while you prevent numbers x and y from being the same, you get two joins; one for the case where x < y and one for the case where x > y, leading to unwanted duplicate joins.

---

Related Topics