Questions tagged [distinct]
The distinct tag has no usage guidance.
141
questions
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
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
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
0
votes
2
answers
7k
views
How can I get distinct counts with different where clauses on the same field? [closed]
How can I get DISTINCT row COUNTs with different conditions in a single SELECT statement, and if count is zero get zero as a result?
I tried the following query:
SELECT DISTINCT
A.DeviceFilter,
...
- 19
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
1
vote
1
answer
5k
views
Using DISTINCT and COUNT in a query
I'm having issues using DISTINCT and COUNT in the same query. I would like to get unique items then get a total count for each month. (I've done this in two queries but I know there's a better way.)
...
- 115
0
votes
2
answers
4k
views
Why is a 'Select distinct limit 100' using tmp tables? [closed]
I hope someone can teach me why MySQL is using temporary tables on a super simple select with a limit of 100 and how to get it do query more efficiently. I'm running MySQL 5.1.53-community.
The ...
- 133
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
0
votes
2
answers
13k
views
Distinct column in an inner join query sql [closed]
I have two tables, linked through Artists Firstnames and surnames. One table has the artists BIOG (only one result per Artist.), the other table has the Artist's images (a lot of images per artist).
...
- 35
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 ...
0
votes
1
answer
48
views
Distinct with timestamp and miliseconds
How can I perform a distinct when the system displays millisecond in my timestamps? I just need the date.
My query is like this:
select distinct orders.client_po,
orders.order_code,...
- 163
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
0
votes
1
answer
211
views
mysql selecting a distinct summed column while having
I have a one-to-many relationship between two tables, where I need to get distinct values out of a summed column b while limited by another summed field a
SELECT
t1.a - sum(t2.a) as a,
t1.b - ...
0
votes
1
answer
249
views
Oracle : Is my DISTINCT function working properly?
I have alphanumeric values as my primary key. I am querying my database in two different ways and finding different results. It is almost certain that I might be missing some key point in my queries. ...
- 179
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
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
0
votes
2
answers
334
views
SELECT DISTINCT TWO columns not ranged [closed]
How can I select distinct two columns not ranged correctly.
for example:
if I have this table t:
================
|| a || b ||
=======||=======
|| 1 || 2 ||
|| 1 || 3 ||
|| 2 || 3 ...
1
vote
2
answers
1k
views
How to select distinct record?
I have a table, as follows:
ID | Name | MyID
1 | Alan | 2
2 | Alan | 2
3 | John | 3
I want to select the records as follows:
If two records have the duplicate ...
- 165
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
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
0
votes
2
answers
2k
views
Which row is selected by DISTINCT?
I have a table with products with names in different languages:
product-id | lang-no | name
Now I want to generate product lists with names in each language. Unfortunately I don't have all names in ...
- 152
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
0
votes
1
answer
501
views
Distinct 3 columns and 4th column with sum [closed]
how can I select in a Oracle DB all columns but some of them DISTINCT.
I have columns A,B,C that I want distinct and column D that I want the sum of the rows.
A B C D
1 2 3 10
1 2 3 ...
- 349
0
votes
1
answer
78
views
Select Unique Fields with Max Values in Each?
I have a table with names, numbers, and timestamps on the data. I want to know how to select a unique instance of each name based on how recent the timestamp is.
Addy (text), NW (number), UDate (...
- 103
1
vote
1
answer
3k
views
Alternative query to avoid count(distinct)
i have a performance issue with my query and i need to rewrite it to have my result in better time. If you can help me with an idea?
My query is the following:
select
dc.Country,
COUNT(...
- 13
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
1
vote
1
answer
24k
views
2 GROUP BY WITH DISTINCT , SUM , COUNT : PHP MYQL
Ref : https://stackoverflow.com/questions/32066097/group-by-with-having-distinct-php-mysql
I got answer (thanks coytech ) but i need one more column in its :
as per :
id | mid | pid | owgh | nwgh |
...
- 123
1
vote
1
answer
90
views
Query - Match Name to Multiple Unique Types
I'm trying to create an Access 2013 database query to display all insurance policy holders that have more than one type of insurance. I am not interested in policy holders that have only one type of ...
- 11
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
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
1
vote
2
answers
1k
views
Get the nth distinct column value ordered by another column
I want to get the row with the nth distinct value in a column, as ordered by another column.
In more real terms, I want the 1000th distinct email_address as ordered by created_at, and the ...
- 125
1
vote
4
answers
2k
views
Grouping Data Using Joined Tables with Certain Distinct Values
I am struggling to get my head around how to do this so please bare with me while I explain.
We have an order system which records orders, order lines, items, users etc.
What I am interested in in ...
- 11
0
votes
2
answers
692
views
MYSQL Select Distinct with SUM
I am developing a Stocks management application, the Portfolio register table has 4 columns, other than date and registry_key, and it looks like this:
----------------------------------------
| ...
- 111
1
vote
4
answers
4k
views
SQL retrieve last elements of table using group by like behaviour
I am trying without success to retrieve the last version of all articles in a table.
The table structure:
CREATE TABLE `journalarticle` (
`uuid_` varchar(75) DEFAULT NULL,
`articleId` varchar(75)...
- 107
0
votes
0
answers
116
views
Retrieving a record for the last date received and returning the value of another column in that row [duplicate]
I have the following query which is returning me total number of expected records :
select a.New_FirstName + ' ' + a.New_LastName as 'Account Name', a.New_MPRNNumber,MAX(b.New_readdate) as 'Last SC ...
- 163
1
vote
2
answers
4k
views
Count of distinct values of a table using sql
name number
=============
lin | 8
lin | 8
lin | 8
rob | 8
rob | 8
rob | 8
lin | 8
lin | 41
lin | 41
rob | 41
and I want the result like
name | Count of 8 | count of 41
====...
- 11
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
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
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
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
1
vote
1
answer
87
views
Result of a query in SQL
Let us say that we have to write a query for:
Find the distinct names of all students with percent>90 in courseno=107
given three relations:
Students (rolno:integer,sname:string)
Courses (...
- 217