Questions tagged [distinct]
The distinct tag has no usage guidance.
146 questions
-1
votes
1
answer
34
views
What happened to Oracle's COUNTDISTINCT() function? [closed]
I need the count of unique values, and am trying to use Oracle's COUNTDISTINCT() function to obtain that:
select COUNTDISTINCT(a.m_label)
from user_rep a, user_group_rep b, trn_grp_rep c
...
- 41.1k
1
vote
1
answer
25
views
Finding the distinct values in an array of documents
I've got a database full of documents which each contain a collection of transactions:
[
{
"key": 1,
"data": [
{
"trans": 1,
"uid&...
- 206
0
votes
1
answer
646
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 with more than 3 millions rows.
There some rows having same values in the first_origin_id, delivery_id fields.
If there are records with same ...
CommunityBot
- 1
0
votes
2
answers
223
views
Selecting a count of distinct logins per day
I have an activity_log table that I need to derive a basic output of how many distinct users logged in that day, and sum up them up day by day. The table logs all actions of a user and will have ...
- 90.3k
2
votes
1
answer
269
views
Emulate Loose Index Scan for multiple columns with alternating sort direction
A while back I asked this question about efficiently selecting unique permutations of columns in Postgres. Now I have a follow-up question regarding how to do so, with the addition of being able to ...
- 182k
2
votes
1
answer
924
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 ...
CommunityBot
- 1
0
votes
1
answer
55
views
Distinct based on window function (sort curiosity or wrong problem approach)
A lot of values are stored in values. The data is in a hierarchical order, so there could be a probe, on which some measurements have been performed. Many points have been archived with strain and ...
- 41.3k
1
vote
2
answers
479
views
Window functions and distinct
I have the following table.
Column | Type | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
code | text ...
- 14.5k
0
votes
0
answers
201
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
103
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 ...
- 39.5k
0
votes
1
answer
45
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 ...
- 898
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,051
1
vote
1
answer
2k
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.6k
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 ...
- 90.3k
15
votes
2
answers
25k
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,...
- 30.6k
0
votes
1
answer
111
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 ...
- 39.5k
0
votes
2
answers
1k
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
31
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. ...
- 4,463
0
votes
0
answers
58
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
282
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,
...
- 16k
0
votes
1
answer
80
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,486
0
votes
1
answer
2k
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,671
-2
votes
1
answer
24
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 ...
- 121
28
votes
5
answers
90k
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 ...
- 182k
0
votes
1
answer
35
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.6k
5
votes
1
answer
2k
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 ...
- 182k
2
votes
1
answer
2k
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 ...
- 182k
-3
votes
1
answer
463
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 ...
- 4,463
0
votes
0
answers
1k
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 ...
- 143
5
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
29
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
18k
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
8
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 ...
- 182k
0
votes
1
answer
175
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
...
- 79.4k
0
votes
1
answer
1k
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
5k
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 ...
- 79.4k
1
vote
1
answer
737
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
...
- 79.4k
1
vote
1
answer
945
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 ...
- 20.3k
1
vote
1
answer
881
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 ...
- 41.3k
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 ...
- 90.3k
0
votes
1
answer
2k
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), (...
- 90.3k
0
votes
1
answer
68
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
63
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
...
- 90.3k
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
...
- 32.4k
1
vote
2
answers
3k
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....
- 182k
-1
votes
1
answer
1k
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 ...
- 20.3k
2
votes
1
answer
426
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
9k
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 ...
- 39.5k
1
vote
1
answer
91
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