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 ...
- 293
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 ...
- 13
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 ...
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
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 ...
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 "...
- 3
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 ...
- 269
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. ...
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 * ...
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,
...
- 103
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 ...
- 245
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 ...
- 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
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
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
-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 ...
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
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, ...
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
...
- 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
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
...
- 113
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
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 ...
- 121
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 ...
- 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), (...
- 397
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
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 ...
- 149
-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 ...
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 ...
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 ...
- 123
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:
+--------------------------------------+---------+--------------------------+----------------------...
- 113
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 ...
- 13
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 ...
- 43
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
0
votes
0
answers
226
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
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
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
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 ...
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