Questions tagged [distinct]
The distinct tag has no usage guidance.
141
questions
1
vote
2
answers
42
views
Window functions and distinct
I have the following table.
Column | Type | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
code | text ...
- 11.8k
0
votes
0
answers
36
views
Optimizing SELECT COUNT(DISTINCT) on a table increasing daily
Let's say we have a table Daily_users which has the columns student_id, school_id, grade, timestamp. We collect usage data of students daily and so the table grows daily (note that there could be ...
- 101
0
votes
1
answer
57
views
The most recent rows in for each type/agent
I have a table (still on MySQL 5.7) with lots of data. This table is written by some scripts. It works like a "checkin", where each agent is checking in in some period of time. It looks like ...
- 35.2k
0
votes
1
answer
38
views
Select distinct
I had some confusion regarding distinct keyword...
For example Table test has 5 columns a b c d e, and column a has repeated value, and I want to use distinct keyword to get distinct values while also ...
- 879
0
votes
1
answer
522
views
Select only those records that have same multiple values for a particular column if it's existing?
Below is an example of my pricing_supports table have more than 3 milions rows.
There some rows having same values in the first_origin_id, delivery_id fields.
If there're records with same ...
CommunityBot
- 1
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 = '...
- 3,031
2
votes
1
answer
110
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 ...
- 54
0
votes
1
answer
225
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 "...
- 23.1k
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 ...
- 83k
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,...
- 29.5k
0
votes
1
answer
83
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 ...
- 35.2k
0
votes
2
answers
519
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
26
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. ...
- 3,780
0
votes
0
answers
35
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 * ...
- 38.9k
0
votes
1
answer
177
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,
...
- 13.9k
0
votes
1
answer
54
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 ...
- 4,391
0
votes
1
answer
975
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 ...
- 1,423
-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 ...
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 ...
- 172k
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 ...
- 23.1k
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 ...
- 172k
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 ...
- 172k
-3
votes
1
answer
356
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 ...
- 3,780
0
votes
0
answers
887
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
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 ...
- 11
1
vote
0
answers
26
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
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, ...
- 2,201
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 ...
- 172k
0
votes
1
answer
69
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
...
- 77.1k
0
votes
1
answer
856
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 ...
CommunityBot
- 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 ...
- 77.1k
1
vote
1
answer
569
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
...
- 77.1k
1
vote
1
answer
716
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
1
vote
1
answer
721
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 ...
- 38.7k
1
vote
0
answers
1k
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
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 ...
- 83k
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), (...
- 83k
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 ...
CommunityBot
- 1
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
...
- 83k
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
...
- 32k
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....
- 172k
-1
votes
1
answer
600
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 ...
- 1
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,...
- 186
-1
votes
1
answer
7k
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 ...
- 35.2k
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 ...
CommunityBot
- 1
0
votes
0
answers
133
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 ...
CommunityBot
- 1
2
votes
1
answer
323
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 ...
- 48k
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 ...
CommunityBot
- 1
1
vote
2
answers
236
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:
+--------------------------------------+---------+--------------------------+----------------------...
- 172k
1
vote
2
answers
760
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 ...
- 172k