Questions tagged [distinct]
The distinct tag has no usage guidance.
141
questions
28
votes
5
answers
89k
views
SELECT DISTINCT on multiple columns
Supposing we have a table with four columns (a,b,c,d) of the same data type.
Is it possible to select all distinct values within the data in the columns and return them as a single column or do I ...
- 1,940
18
votes
1
answer
1k
views
DISTINCT not reducing two equal values to one
Can anyone explain the below situation, where two seemingly equal values are not reduced by DISTINCT?
The query above is SELECT DISTINCT name FROM master.sys.dm_os_spinlock_stats where name = '...
- 519
14
votes
1
answer
98k
views
Get rows having different values for a column based on the duplicate values of combination of other 3 columns
I want to get only rows having a different values in a column(column name DEF) based on the duplicate rows having unique combination of other 3 columns.
Example: In the below example first two rows ...
- 893
14
votes
2
answers
93k
views
Select only those records that have different/multiple values for a particular column
Below is an example of my membership table. There some records having multiple values in the email field. I only want to select those records that have multiple email values:
Member table
ID ...
- 333
13
votes
2
answers
20k
views
SELECT DISTINCT ON, ordered by another column
Please consider the following table test:
CREATE TABLE test(col1 int, col2 varchar, col3 date);
INSERT INTO test VALUES
(1,'abc','2015-09-10')
, (1,'abc','2015-09-11')
, (2,'xyz','2015-09-12')
, (2,...
- 317
9
votes
3
answers
29k
views
DISTINCT on one column and return TOP rows
How do you query for three unique customers with the largest Purchase_Cost?
I want to apply the DISTINCT only on Customer_Name, but the query below applies the distinct on all three columns. How ...
- 93
8
votes
5
answers
26k
views
SQL queries to only show most recent purchase records for individual food items
I'm working with a food purchasing / invoice system in MS Access 2013 and am trying to create an SQL query that will return the most recent purchase price for each individual food item.
Here is a ...
- 379
8
votes
2
answers
14k
views
Get the last 5 distinct values for each ID
I'm working with PostgreSQL 9.4.
I have a table that contains the following entries:
id | postcode | date_created
---+----------+-----------------
14 | al2 2qp | 2015-09-23 14:46:57
14 | al2 ...
- 95
8
votes
1
answer
583
views
Is it possible to get seek based parallel plan for distinct/group by?
An example from this question shows that SQL Server will choose a full index scan to solve a query like this:
select distinct [typeName] from [types]
Where [typeName] has a non-clustered, non-unique ...
- 2,078
7
votes
2
answers
3k
views
Is there a way to SELECT n ON (like DISTINCT ON, but more than one of each)
I have a table us_customers that looks like this (with hundreds of thousands of rows):
+----------+----------+
| id | us_state |
+----------+----------+
| 12345678 | MA |
| 23456781 | AL ...
- 331
7
votes
2
answers
17k
views
MySQL: Using DISTINCT and GROUP BY together?
I saw the following MySQL query that that uses both DISTINCT and GROUP BY together:
SELECT DISTINCT user_id, post_id, post_content
FROM some_table
GROUP BY post_id, user_id
HAVING ...
7
votes
1
answer
3k
views
Why do two identical strings have a different length but the same binary value?
I am trying to return a distinct set of department names from a table - nothing special. However, when using the following query duplicates are displayed:
select distinct department_name
from ...
- 13k
6
votes
1
answer
16k
views
Calling SELECT DISTINCT on multiple columns
I am unclear on exactly on,
SELECT DISTINCT col1, col2, ...
FROM table_name
When called on one column it gives back only distinct values of that column. What happens when we use it with multiple ...
- 163
6
votes
2
answers
4k
views
How to create aggregate function, like sum, with an implicit DISTINCT?
We have an ERP system which allows aggregates to be used (eg SUM(foo)) but not with DISTINCT (eg SUM(DISTINCT foo).
Is it possible to create an aggregate function (SUM_DISTINCT), that returns the ...
- 217
6
votes
2
answers
40k
views
Distinct Combination of Two Columns
I am currently trying to find distinct combinations within a table as two of the columns have a many to many relationship with each other.
The data is all around backup policies being run on against ...
- 63
6
votes
1
answer
11k
views
Postgres: Insert distinct values from one table into another table with constrains in place
I'm trying to insert data from one PostgreSQL 9.3 table to another while removing duplicate entries.
I have two tables containing email addresses.
The main table contains emails and a tag for each ...
- 63
6
votes
1
answer
2k
views
Fastest query for distinct IDs in a many-to-many relationship
I have this table in PostgreSQL 9.4:
CREATE TABLE user_operations(
id SERIAL PRIMARY KEY,
operation_id integer,
user_id integer )
The table consists of ~1000-2000 different operations ...
- 1,195
5
votes
2
answers
35k
views
Change SELECT DISTINCT to UPDATE DISTINCT
How can I modify my SELECT DISTINCT query to an UPDATE DISTINCT query?
It is important that it only updates the distinct records, as there are multiple records associated with each [...
- 231
5
votes
2
answers
2k
views
DISTINCT column combination with permutations
My postgresql column structure looks like this:
id | from | to
---------------
1 | A | B
2 | A | B
3 | C | D
Now I want an result which looks like this:
res
-----
'A:B'
'B:A'
...
- 196
5
votes
1
answer
1k
views
Can PostgreSQL use indexes to expedite count(distinct) queries?
Consider the following example:
CREATE TABLE test (
id SERIAL,
some_integer INT
);
INSERT INTO test (some_integer)
SELECT FLOOR(RANDOM()*100000) from generate_series(1,100000) s(i);
CREATE INDEX ...
- 153
5
votes
1
answer
5k
views
Is there a way to speed up a DISTINCT query?
I have a table t in a database (PostgreSQL 10.4):
\d t;
Table "public.t"
Column | Type | Collation | Nullable | Default
----------+------------------------+------...
- 153
5
votes
1
answer
20k
views
Counting unique (distinct) users per day
I am new topsql and am interested in counting the number of unique first time users per day.
My table only has two columns- user_id (there can be multiple uses by one user per day) and start_time ...
- 219
5
votes
1
answer
2k
views
Tolerating SQL errors (orderby not in distinct, select not in group by) in MySQL 5.7.13 vs 5.7.11
This question is related to the following 2 SQL errors (line breaks added by me)
MySql.Data.MySqlClient.MySqlException (0x80004005):
Expression #2 of ORDER BY clause is not in SELECT list, references ...
4
votes
5
answers
1k
views
Select same records that only have different values for a particular column
Below is an example of my customer table. There some records having multiple values in BIRTHDAY DATE (by mistake or so). I only want to select those records that have same values for LASTNAME, ...
- 41
4
votes
2
answers
3k
views
What's the difference between DISTINCT_SCAN and IXSCAN in explain output?
In Mongo 3.4 explain("executionStats").distinct(...), for some queries
the winning plan contains an IXSCAN step, and for other queries it contains an DISTINCT_SCAN step.
What is the difference ...
- 297
4
votes
3
answers
2k
views
Find values which occur in every row for every distinct value in other column of the same table
I need a select statement which finds values from Column B which occurs in all distinct values for A. Meaning in my example it would return: x since x occurs in every value of A. (1,2,3 and 4)
I don'...
- 1,353
4
votes
1
answer
2k
views
performances of distinct/group by
I'm trying to select the most recent authors of messages in a room, using this (simplified) table:
Table "public.message"
Column | Type | Nullable | Default ...
- 185
3
votes
3
answers
1k
views
Select distinct column values from multiple databases
I have 100 databases from customers who all have the same schema but different content.
Now I wanted to some analysis and start out with running a distinct of one column over all databases. My ...
- 131
3
votes
2
answers
6k
views
Select most recent row by some other column
I'd like to select the most recent row in a MS SQL Server 2008 R2 database based on a couple of factors. I've seen a lot of people want to do the same thing online, and a lot of different proposed ...
3
votes
2
answers
2k
views
Improving distinct values estimates in Postgres
Full counts in Postgres can be slow, for reasons that are well-understood and much discussed. So, I've been using estimation techniques instead, where possible. For rows, pg_stats seems fine, for ...
- 869
3
votes
1
answer
13k
views
Select Distinct with Inner Join Bringing duplicated Values
I am having trouble using inner join to bring unique values from two tables.
SELECT distinct cast (opd.order_number as integer), p.payment_id ,
pt.transaction_status,
opd.installments_number, ...
3
votes
2
answers
143
views
LEFT JOIN confusion
I am having issues with this simple scenario. I'm probably missing some basic concept...
I have 2 tables, the first of "things" and the second of orders. I've created a sqlfiddle: http://sqlfiddle....
- 133
3
votes
2
answers
2k
views
Rank vs. Distinct
Just for curiosity, if I have to use rank to tag distinct values for some columns only, and ignore whatever different values occurred on other columns, why not use distinct and just remove the other ...
- 1,330
3
votes
3
answers
669
views
Why does the CBO eliminate `distinct` and not `group by` in this query?
The CBO chooses to eliminate the distinct in the 'slow' query - presumably it can tell that the operation isn't needed because of the outer group by.
My questions are:
1) Why does it choose to do so ...
- 39.7k
3
votes
1
answer
1k
views
inconsistent distinct + order by behavior between mysql 5.6 and 5.7
I use MySQL 5.6 and 5.7 on Ubuntu 16.04.3 LTS and I have a problem with the following query (which is derived from drupal 7 but have simplified it in order to pinpoint the issue):
select distinct ...
- 171
3
votes
1
answer
12k
views
SQL Trying to sum and group by distinct values
I have a table
+-------+----------+------------+----------+---------+
| Plant | LineName | WorkCenter | Material | ProdQty |
+-------+----------+------------+----------+---------+
| x | xl |...
- 33
2
votes
1
answer
7k
views
Grouping based on the status, then count its product and the total based on its category ID
I have a table with product ID, category ID and status of the product.
+---------+-------------+-------------+
| id | cat_id | status |
+---------+-------------+-------------+
| 1 ...
- 159
2
votes
1
answer
15k
views
Select unique value pairs that occur (count) more than once inside a table
How can I retrieve an row with unique value pairs that occur more than once...
Heres an example of what I'm what I am trying to..
SELECT
column_fk_id1, column_fk_id2,
COUNT(distinct column_fk_id1, ...
- 23
2
votes
1
answer
2k
views
Count all rows for each DISTINCT
How can count all rows of WHERE statement:
SELECT DISTINCT column1 FROM table COUNT WHERE date::text LIKE '%2014%';
My output:
column1
=======
info1
info2
info3
Desired OUTPUT:
column1|...
- 149
2
votes
3
answers
422
views
Display summary results based on empty value in another column
I need some assistance and guidance.
I want to retrieve a summary of results based on values in another column.
For example, I have a column in my data set that shows an audit date. There will be ...
2
votes
1
answer
1k
views
Best performance to get distinct values for a given key from a big table
Given a table like so:
CREATE TABLE attr (user_id uuid, key text, value text);
with indexes on key and value (but not a combined index) what are my options for getting a list of distinct values by ...
- 251
2
votes
1
answer
322
views
Why does Type Resolution fail when using SELECT DISTINCT instead of SELECT on PostgreSQL?
Here is a minimal reproduction:
SELECT 1
UNION ALL
SELECT DISTINCT NULL
Fails with:
UNION types numeric and text cannot be matched
According to the PostgreSQL documentation on Type Resolution for ...
- 123
2
votes
1
answer
11k
views
Distinct in one column where another column has same values
I have a table sample
id | name | type
-------+--------+------
PR001 | DC001 | a
PR002 | DC001 | a
PR003 | DC001 | b
PR004 | DC002 | a
PR005 | DC003 | a
PR006 | DC003 ...
- 23
2
votes
1
answer
3k
views
Summation of distinct rows - SQL Server
I am pretty sure I am making a basic mistake, but I can't figure it out for the life of me. I am trying to get a simple report of total number of unique employees, number of employees who are male, ...
- 21
2
votes
1
answer
1k
views
select distinct on csv data
A table has CSV data and I want to do a SELECT DISTINCT ID on that table, how should I proceed?
+-----------+
| id |
+-----------+
| 111,1487 |
+-----------+
| 462 |
+-----------+
| ...
- 1,364
2
votes
2
answers
4k
views
How to "flatten" multiple rows with same ID (Oracle 11g) by concatenating **multiple** fields per row?
I'm connected to an Oracle Database (11g Release 2 - 11.2.0.4).
I'd like to "flatten"/"merge" all rows with the same ID, pretty much as is well delineated here: https://www.w3resource.com/mysql/...
2
votes
1
answer
362
views
Oracle DISTINCT with NULLable columns
There is a longstanding(?) problem(?) with Oracle that if you want to use a DISTINCT select on a column which has a B*-Tree Index but is nullable, that the index is not used. (As other answers suggest,...
- 1,456
2
votes
1
answer
109
views
DISTINCT gives duplicate values
Can it be considered a bug? This query gives duplicate values despite DISTINCT:
select distinct '1'
from
(
select *
from dual
connect by level <= 10
)
order by dbms_random.value
fetch first 10 ...
2
votes
0
answers
243
views
Optimize SELECT DISTINCT (with existing index) to avoid sequential scan [duplicate]
I have a records table with ~3 million records. The table has a column called record_type with an index.
Table "public.records"
Column | ...
- 341
1
vote
2
answers
1k
views
Does the EXIST clause only return distinct rows by its nature?
I have two queries that appear to be logically equivalent, yet they return different result sets. I am using the AdventureWorks2012 database.
The first query uses an EXISTS clause with a subquery:
...
- 311