Skip to main content
16 votes
Accepted

DISTINCT not reducing two equal values to one

It looks like the DISTINCT gets optimised out during simplification. select distinct name FROM master.sys.dm_os_spinlock_stats OPTION (QUERYTRACEON 8606,QUERYTRACEON 3604) Gives *** Input Tree: *** ...
Martin Smith's user avatar
  • 84.9k
13 votes
Accepted

SELECT DISTINCT ON, ordered by another column

You can still use DISTINCT ON. Just wrap it into an outer query to sort to your needs. See: Get distinct on one column, order by another PostgreSQL DISTINCT ON with different ORDER BY SELECT * FROM ...
Erwin Brandstetter's user avatar
11 votes
Accepted

Calling SELECT DISTINCT on multiple columns

How does this work exactly? It gives you distinct combinations of all the expression in the SELECT list. SELECT DISTINCT col1, col2, ... FROM table_name ; is also equivalent to: SELECT col1, ...
ypercubeᵀᴹ's user avatar
9 votes
Accepted

How to create aggregate function, like sum, with an implicit DISTINCT?

Is it possible to create an aggregate function (SUM_DISTINCT), that returns the same result as as SUM(DISTINCT foo), so SUM_DISTINCT(foo) = SUM(DISTINCT foo)? Yes, it is possible — you need a User-...
Jack Douglas's user avatar
9 votes
Accepted

DISTINCT on one column and return TOP rows

Replace your dbname and schemaName in the following query. ;WITH CTE AS ( SELECT [Order_No] ,[Customer_Name] ,[Purchase_Cost] , ROW_NUMBER() OVER(PARTITION BY [customer ...
SqlWorldWide's user avatar
  • 13.4k
9 votes
Accepted

Can PostgreSQL use indexes to expedite count(distinct) queries?

There is a separate entry in a B-tree index for every row, for duplicates, too. So we can never "simply count the number of rows in the index". There are as many index rows to read and ...
Erwin Brandstetter's user avatar
8 votes
Accepted

Select unique value pairs that occur (count) more than once inside a table

What you have almost works, just remove the distinct and change the > 2 to > 1. The distinct is not necessary as the grouping handles that and the > 2 is looking for things that have at least three ...
Leigh Riffel's user avatar
  • 23.9k
8 votes
Accepted

Counting unique (distinct) users per day

Because your form is off, what you want is SELECT count(DISTINCT x) FROM generate_series(1,10) AS gs(x); Or, in your case, SELECT start_time::date, count(DISTINCT user_id) FROM mytable GROUP BY ...
Evan Carroll's user avatar
  • 63.7k
8 votes
Accepted

Is there a way to speed up a DISTINCT query?

As suggested, an index on (site, sn) could speed this up, especially if the table is well-vacuumed so that it benefits from index-only scans. If that is not enough of a speed up, then there is a way ...
jjanes's user avatar
  • 40.2k
8 votes

SELECT DISTINCT ON, ordered by another column

This is a classic greatest-n-per-group problem. They frequently arise in a whole host of areas and, like Window functions (see below) are well worth studying. Nowadays, it is typically solved by using ...
Vérace's user avatar
  • 30.2k
6 votes
Accepted

Distinct Children rows ordered by a column of Parent

You need to GROUP BY instead of DISTINCT (the effect is the same) and you need to aggregate the column you want to use for sort order. In this case I used MIN, but you can use whatever makes sense ...
spaghettidba's user avatar
  • 11.3k
6 votes

Why do two identical strings have a different length but the same binary value?

Good job investigating so far. Some initial notes: I wouldn't worry about that function from the S.O. answer. RTRIM and LTRIM only trim spaces, not white-space in general: SELECT RTRIM('A ') + '...
Solomon Rutzky's user avatar
6 votes
Accepted

Count all rows for each DISTINCT

In this case you can use GROUP BY to get distinct column1 values, and instead of convert the date to text you can use EXTRACT function for this purpose. select colum1, count(*) as ...
McNets's user avatar
  • 23.8k
6 votes
Accepted

DISTINCT column combination with permutations

This looks more like a stack overflow question yet Union will give a distinct combination. SELECT concat("from",':',"to") as res From Table UNION SELECT concat("to",':',"from") From Table
Ajan Balakumaran's user avatar
6 votes
Accepted

Improving distinct values estimates in Postgres

First, a remark: your query could be written simpler as SELECT count(DISTINCT assembly_id) FROM assembly_prods; Also, your statistics query is wrong, because n_distict can also be negative. You ...
Laurenz Albe's user avatar
  • 53.4k
6 votes
Accepted

Select distinct column values from multiple databases

There were some issues with an earlier version of another answer, so I'm going to put this here, as a slightly different version of the same thing: String aggregation via variable should be avoided, ...
Charlieface's user avatar
  • 13.3k
5 votes
Accepted

Select most recent row by some other column

If there are more than one AnalogValue, you can get max value for each RoomId and then JOIN with CRV_AttributeLog just to fetch all attributes. WITH maxTime as ( SELECT RoomId, MAX(LogTimeStamp)...
McNets's user avatar
  • 23.8k
5 votes

How can I get distinct counts with different where clauses on the same field?

It seems like you really want a PIVOT. First, we'll create your DeviceLicense table, and populate some sample data: CREATE TABLE dbo.DeviceLicense ( SINo int NOT NULL , DeviceFilter nvarchar(...
Hannah Vernon's user avatar
  • 70.2k
5 votes
Accepted

Postgres: Insert distinct values from one table into another table with constrains in place

There are 3 possible kinds of duplicates: Duplicates within the rows of the bulk insert. Duplicates between inserted rows and existing rows. Duplicates between inserted rows and concurrently inserted ...
Erwin Brandstetter's user avatar
5 votes
Accepted

Is a UNION query equivalent with or without DISTINCT in subqueries?

No, they are not necessarily "treated the same by the database engine." A test below shows that you might get different query plans. In many cases, the difference between query plans may not matter ...
Geoff Patterson's user avatar
5 votes
Accepted

How to query results from a huge postgres database?

SELECT COUNT (DISTINCT column_name) FROM table_name; The part of the PostgreSQL code that implements "COUNT(DISTINCT...)" is quite old and hasn't had much performance work done on it recently. It ...
jjanes's user avatar
  • 40.2k
5 votes

MySQL: Using DISTINCT and GROUP BY together?

The insane ability to allow partial group by in older versions of MySQL, has to be one top contender for most caused confusion in the it industry. Given the table: CREATE TABLE t ( x int not null ...
Lennart - Slava Ukraini's user avatar
5 votes
Accepted

Select Distinct with Inner Join Bringing duplicated Values

DISTINCT will remove duplicate result rows, that is rows where all columns are equal. If you want only one result row per payment_id, there are two options: Use SELECT DISTINCT ON (payment_id) to ...
Laurenz Albe's user avatar
  • 53.4k
5 votes
Accepted

Use skip scan index to efficiently select unique permutations of columns in Postgres

The CTE emulation of skip scan index can be generalized to n columns using Row Constructor Comparison WITH RECURSIVE cte AS ( ( SELECT column1, column2, column3 FROM table_data ORDER BY ...
Andrea B.'s user avatar
  • 1,613
4 votes
Accepted

Does the EXIST clause only return distinct rows by its nature?

EXISTS don't actually return any rows. They check for the existence and then move on. Your problem is probably a duplicate caused by the JOINs to the second two tables. Try running this: SELECT p....
Kenneth Fisher's user avatar
4 votes

SELECT DISTINCT TWO columns not ranged

A more efficient (and somewhat shorter) version of what Jon of All Trades mentioned in his answer could be something like this: SELECT DISTINCT CASE WHEN A < B THEN A ELSE B END AS value1, CASE ...
david1602's user avatar
4 votes
Accepted

Distinct in one column where another column has same values

SELECT name, MIN(type) AS type FROM table_name GROUP BY name HAVING COUNT(DISTINCT type) = 1 ; or the same with: HAVING MIN(type) = MAX(type) I assume that type is not nullable. If you want ...
ypercubeᵀᴹ's user avatar
4 votes

Distinct Children rows ordered by a column of Parent

As @ypercube has pointed out on his comments, You can use GROUP BY and ORDER BY in the same sentence. create table parent(id int, createdDate date); insert into parent values (1, '20170301'),(2, '...
McNets's user avatar
  • 23.8k

Only top scored, non community-wiki answers of a minimum length are eligible