Questions tagged [distinct]
The distinct tag has no usage guidance.
137
questions
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
2
votes
1
answer
89
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 ...
0
votes
1
answer
125
views
Insert rows into a (postgres) table where the first col is a static value and the second col is the result of a SELECT DISTINCT from another table
I haven't had to use SQL in years and I'm clearly rusty.
I have a situation where I'm trying to insert multiple rows into table A.
There are two columns of data to be inserted, the first is a "...
- 3
0
votes
1
answer
72
views
Fetching 88 records via DISTINCT from a table of 1.5 million rows is taking 3 seconds
There is a table which are populated from an online service and updated every week.
This goes on a separate (second) database.
The table is like 300MB in size (1.5 million rows) processed by a cron ...
- 269
0
votes
2
answers
360
views
PostgreSQL, INSERT INTO SELECT DISTINCT generates more rows?
I have a large PostgreSQL table (NOT indexed) with duplicates so I ran the following query to eventually end up with a unique rows table:
INSERT INTO newTable(Field1, Field2, Field3)
SELECT DISTINCT ...
- 11
0
votes
1
answer
25
views
Fetch distinct / different from previos row Data from MySQL Table
I have a simple Table. It contains an ID, a Temperature and a Date. Every 10 Seconds a Sensor writes a Row with the measured Temperature-Value.
What I need is the Data, where the temperature changed. ...
0
votes
0
answers
32
views
Select columns from joined tables, group or distinct by a single column, and order by another column
Here's essentially what I'm trying to do:
SELECT
deals.*,
retailers.title AS retailer_title,
ST_Distance(retailers.lonlat, 'SRID=4326;POINT(-118.4104684 34.1030032)' :: geography, 15 * ...
0
votes
1
answer
120
views
How to structure an index to support counting distinct values of one column and grouping by several others?
I have a single reporting table of sales data with about 4 million rows of data:
CREATE TABLE reporting_sales (
customer_id bigint(20) DEFAULT NULL,
effective_date date DEFAULT NULL,
...
- 103
0
votes
1
answer
36
views
Sort and Select by distinct field when using an aggregate function
I want to select all clicks and views for certain objects. Clicks and views are tracked in separate tables, but I want 1 row returned for an object with the views and clicks combined. This works ...
- 245
0
votes
1
answer
729
views
Use skip scan index to efficiently select unique permutations of columns in Postgres
I've already read this excellent answer for emulating skip scan index to retrieve the distinct values for a single column. This is blazingly fast.
Now what I'm trying to do is to retrieve unique ...
- 165
-2
votes
1
answer
22
views
Extracting data from two tables
I have( for example) a table called A and a table called B.
Table A contains tons of user data( age, country bla bla) and is connected to B by forein_key=id( B only has ID/NAME.
I want to find out how ...
- 1
0
votes
1
answer
34
views
Can't get this GROUP BY or DISTINCT right
On MySQL 5.7.34 I have this simple db structure. Important columns are domain name, domain total links in, but entries are duplicate (once for each found link from various sources), I need to select ...
- 195
4
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 ...
- 143
1
vote
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 ...
- 241
-3
votes
1
answer
308
views
choosing oldest valued cell when 2 columns have same value but third column has differing values (SQL)
In SQL, I have three columns, the first two are values to create distinction for a person. The third column will have different values for each person (dates). How do I select the row for each person ...
0
votes
0
answers
820
views
Get distinct pairs with multiple columns on table with next row
I currently have a table that has a list of locations a schedule will use and I want to be able to get the location code and platform for both the current row and the next one, and then have the ...
- 133
1
vote
0
answers
25
views
Greedily select rows that match against rules for each group
Suppose there's a product table with the following fields
id (pk), category (index), name, picture, stock
and we want to find ONE product in each of these category (1, 2, 3) that match at least one ...
- 111
3
votes
1
answer
12k
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, ...
0
votes
1
answer
37
views
List out distinct attributes in one column for distinct IDs [duplicate]
right now I am getting duplicates on an ID because it has unique attributes. Example and query below
Current:
Select distinct ID, Company
from Order
ID
Company
1100
Fisher
1100
Nesses
1101
Denny's
...
- 1
1
vote
4
answers
4k
views
Select the rows only when all sub columns contain the same value [duplicate]
Suppose I have 3 columns, Item, SubItem, Number.
I want to select the rows only when all the subitems have number as 0.
For example in the below table, only Phone and Pant should be selected since all ...
- 11
1
vote
1
answer
518
views
Optimizing a distinct on a parent table in a multi-level join in MySQL 5
I have the following tables: in a MySQL 5 database:
projects - 1,000 rows
tasks (FKed to projects) - 10,000 rows
task_tags (FKed to tasks) - 350,000 rows
task_comments (FKed to tasks) - 750,000 rows
...
- 113
1
vote
1
answer
638
views
Find Max Date from a Distinct Row Then Add Value to a Table Column
Quick disclaimer - I am familiar with Access, but a novice at MySql within Access.
I have a table with activity data that I pulled into a query to filter down to only necessary data. The query table ...
1
vote
1
answer
670
views
DISTINCT forces re-sorting of data on PostgreSQL
I have a table recording transactions between accounts, looking like this:
txid varchar(64)
account varchar(64)
ts timestamp
amount numeric
(there are more fields but they are irrelevant for ...
- 121
1
vote
0
answers
929
views
MariaDB JSON_ARRAYAGG gives wrong result
I have 2 problems in MariaDB 15.1 when using JSON_ARRAYAGG
1. The brackets [] are omitted
2. wrong result, values are duplicates or omitted
My database is the following:
user:
+----+------+
| id | ...
3
votes
3
answers
976
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
0
votes
1
answer
1k
views
PostgreSQL DISTINCT ON forces a specific order
Why DISTINCT ON is sorting the rows? E.g. the query
SELECT DISTINCT ON (a) a
FROM (
VALUES (2), (3), (1)
) x(a);
will have the result
1
2
3
while without DISTINCT ON
SELECT a
FROM (
VALUES (2), (...
- 377
0
votes
1
answer
66
views
need the rows with a unique value on certain columns but date column doesn't help?
I have a table with multiple records by each employee. I need to find all the records by employee where any of the columns values changes. For example, I have the following data for George:
CREATE ...
- 15
0
votes
1
answer
40
views
SELECT only the rows with a unique value on certain columns [closed]
I have a table with multiple records by each employee, i need to find all the records by employee where any of the columns values changes. for example i have the following data for George:
HOME
...
- 15
1
vote
2
answers
2k
views
SELECT DISTINCT in extra space data from a single column ( no duplicates )
Hello i am having problems with this column data
cities
Charlotte
Cincinnati
Cleves
Columbus
Fairfield
Fresno
Los Angeles -- HERE IS THE PROBLEM
Los Angeles
Madison
Manhatttan Beach
New York
Orange
...
- 135
1
vote
2
answers
2k
views
GROUP BY, but use only one row per user
I've spent several hours to make a "simple" SELECT with GROUP BY in Postgres without success. The GROUP BY clause is giving me problems.
I have the table cities with columns user_id and city....
- 113
-1
votes
1
answer
495
views
How to get distinct count on sub table joined via two different way
I have 3 below tables in my db
CREATE TABLE main_table
(
id SERIAL PRIMARY KEY,
name VARCHAR(255) UNIQUE NOT NULL
);
CREATE TABLE middle_table
(
id SERIAL PRIMARY KEY,
name ...
- 149
-1
votes
1
answer
6k
views
select distinct all the customer who have more than one account [closed]
I have two tables customer and account
customer can have more than one account
i would like to select only the customers who have more than one account
in the tables below.. i would like to joind the ...
0
votes
0
answers
131
views
How can I update query to use GROUP BY instead of DISTINCT while still preserving ORDER BY?
Trying to make this query more performant (basically I need to have distinct speech_ids from the speech_candidate table while at the same time counting the number of duplicate speech_ids for each of ...
2
votes
1
answer
310
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
1
vote
2
answers
228
views
select rows on distinct column value without vendor-specific sql syntax
I'm trying to select distinct rows without any vendor specific syntax. My table layout is as follows:
+--------------------------------------+---------+--------------------------+----------------------...
- 113
1
vote
2
answers
677
views
Sum all unique values from previous dates
Let's say, for simplicity sake, I have the following table:
id amount p_id date
------------------------------------------------
1 5 1 2020-01-01T01:00:00
2 10 ...
- 13
0
votes
1
answer
814
views
Substitute DISTINCT ON for query transformation (Postgresql to Redshift)
I need to transform my Postgresql query to Redshift and I working with the following data, here is an excerpt:
In my final output, I would like only to keep rows with the highest rank (in green). And ...
- 43
3
votes
1
answer
11k
views
SQL Trying to sum and group by distinct values
I have a table
+-------+----------+------------+----------+---------+
| Plant | LineName | WorkCenter | Material | ProdQty |
+-------+----------+------------+----------+---------+
| x | xl |...
- 33
0
votes
0
answers
219
views
Postgresql - slow distinct on million records table
I have a table with a million of records, i need to select the distinct values of log_type.
In other queries i also need to filter log_types result of the distinct and limit the result.
But the query ...
1
vote
0
answers
29
views
How to return most recent element with distinct subquery
I have such query but it returns duplicated rows for same speaker
SELECT
"speakers"."id",
"speakers"."name",
"speakers"."surname",
"speakers"."photo_url" as "photoURL",
"talks"."...
- 123
13
votes
2
answers
19k
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
0
votes
0
answers
25
views
How to retrieve rows with max value in one column for each combination of other two columns? [duplicate]
I need to query an SQL database to find all records with max RATE for each combination of fields A and B. For example, consider the following table with 4 columns, A, B, RATE & VALUE:
A B RATE ...
- 101
0
votes
1
answer
29
views
Need to Bring Back most Recent Departure Date from Sales table for Customers in Recipient table
Looking at 3 Tables - Recipient, HouseholdScores, Sales.
I need to bring back the most Recent tsDepart (departure date) from sales for the iCustomerId in the Recipient Table. There is no Depart date ...
7
votes
2
answers
15k
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 ...
0
votes
1
answer
3k
views
ORDER BY items must appear in the select list if SELECT DISTINCT is specified
SELECT DISTINCT *
FROM (
SELECT a.Title, a.ID FROM TableA a
LEFT JOIN TableB b
ON a.ID = b.XID) c
ORDER BY
CASE WHEN c.ID > 10 THEN c.Title
ELSE c....
- 111
0
votes
0
answers
1k
views
Remove/distinct duplicates on listagg - oracle
I'm using the oracle-xe 11g version and I have the below sql code that I aggregation the course code, but the output has duplicates.
SELECT
b.alu_cpf AS login,
b.alu_nomeco ...
- 1
0
votes
1
answer
41
views
Filter the query result
I have one query by which I am getting duplicate ComapnyID and I want to filter that and remove duplicate ComapnyID but don't want change the order by report.
table 1 reports
Table 2 Company
Here ...
- 3
0
votes
1
answer
147
views
Distinct on two columns, how to rid from 'duplicates'
I have a table with messages, that have 'msg_from' and 'msg_to' fields.
when I want to select all current chats and last message from each of them I use the following query:
select distinct on (...
0
votes
1
answer
49
views
MongodB cumulated conditionnal array:using map/reduce or aggregation
First, I aplogized because I have some problem to formalized my question so any idea on express it correctly will be appreciated and edited.
Last try:
How to get last of a cumulated conditionnal ...
- 101
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 ...
- 849