Questions tagged [distinct]
148 questions
29
votes
5 answers
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 have to create a function to achieve this?
Fabrizio Mazzoni
- 1,976
- 5
- 22
- 32
19
votes
1 answer
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 = 'SBS_UCS_DISPATCH';
The equivalent SELECT name FROM…
jimbobmcgee
- 529
- 2
- 11
17
votes
1 answer
Union does not always eliminate duplicates
I have the following query and expect that as a result I will have a list of IDs without duplicates. But sometimes it produces duplicates (1-2 on 4 million rows).
Why can it happen? I run it with the default (read committed) isolation level. I can't…
Novitskiy Denis
- 331
- 1
- 11
16
votes
2 answers
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,'xyz','2015-09-13')
, (3,'tcs','2015-01-15')
,…
Luis
- 347
- 2
- 3
- 9
15
votes
1 answer
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 has same value for first 3 columns.But they have…
Navaneet
- 923
- 5
- 12
- 23
14
votes
2 answers
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 LASTNAME FIRSTNAME EMAIL
567 Jones Carol …
AlGator
- 333
- 1
- 2
- 5
9
votes
3 answers
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 should I modify the query to obtain the desired…
Neal
- 93
- 1
- 1
- 3
9
votes
2 answers
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 2qp | 2015-09-23 14:51:07
14 | sp2 8ag |…
Rebex
- 115
- 1
- 1
- 6
8
votes
1 answer
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 ascending index on it. His example has 200M rows…
crokusek
- 2,110
- 4
- 25
- 34
8
votes
2 answers
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 post_content LIKE '%abc%';
Here is a scenario to go…
oneCoderToRuleThemAll
- 181
- 1
- 1
- 4
8
votes
2 answers
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 |
| 34567812 | GA |
| 45678123 | FL …
Shaun Scovil
- 341
- 1
- 5
- 11
7
votes
1 answer
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 dbo.departments;
I also tried:
select distinct…
Mr.Brownstone
- 13,242
- 4
- 38
- 55
7
votes
5 answers
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 diagram of the tables I'm working with:
My…
J. Taylor
- 379
- 2
- 5
- 17
7
votes
2 answers
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 particularly clients and could be summarized as…
TheGrew
- 73
- 1
- 1
- 4
6
votes
1 answer
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 columns and should we ever do this?
RhythmInk
- 163
- 1
- 1
- 5