Questions tagged [distinct]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
2 answers
73 views

Window functions and distinct

I have the following table. Column | Type | Collation | Nullable | Default ----------+-----------------------------+-----------+----------+--------- code | text ...
Zephyrus's user avatar
  • 301
0 votes
0 answers
39 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 ...
dezdichado's user avatar
0 votes
1 answer
74 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 ...
Marek's user avatar
  • 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 ...
datascinalyst's user avatar
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 = '...
jimbobmcgee's user avatar
2 votes
1 answer
228 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 ...
Andy DB Analyst's user avatar
0 votes
1 answer
423 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 "...
Amy Lee's user avatar
0 votes
1 answer
89 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 ...
anjanesh's user avatar
  • 279
0 votes
2 answers
670 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 ...
Anvil's user avatar
  • 11
0 votes
1 answer
27 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. ...
user39063's user avatar
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 * ...
JohnDoe1999's user avatar
0 votes
1 answer
222 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, ...
Yardboy's user avatar
  • 103
0 votes
1 answer
69 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 ...
Adam's user avatar
  • 245
0 votes
1 answer
1k 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 ...
hunter's user avatar
  • 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 ...
Gal Mor's user avatar
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 ...
adrianTNT's user avatar
  • 195
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 ...
Shane's user avatar
  • 153
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 ...
Chris Cooper's user avatar
-3 votes
1 answer
389 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 ...
mcsmithums's user avatar
0 votes
0 answers
919 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 ...
Liam's user avatar
  • 133
1 vote
0 answers
28 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 ...
Ian Chen's user avatar
  • 111
3 votes
1 answer
15k 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, ...
Felipe Chermont's user avatar
0 votes
1 answer
99 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 ...
emzsil's user avatar
  • 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 ...
Tridib Roy's user avatar
1 vote
1 answer
618 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 ...
waiwai933's user avatar
  • 113
1 vote
1 answer
783 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 ...
Andres Martinez's user avatar
1 vote
1 answer
758 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 ...
StasM's user avatar
  • 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 | ...
Salvador Real's user avatar
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 ...
edelwater's user avatar
  • 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), (...
Radu Dumbrăveanu's user avatar
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 ...
Jen's user avatar
  • 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 ...
Jen's user avatar
  • 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 ...
rubengavidia0x's user avatar
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....
Wonton's user avatar
  • 113
-1 votes
1 answer
720 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 ...
Tanweer's user avatar
  • 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 ...
Robert Salem's user avatar
0 votes
0 answers
137 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 ...
AdjunctProfessorFalcon's user avatar
2 votes
1 answer
344 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 ...
soc's user avatar
  • 123
1 vote
2 answers
239 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: +--------------------------------------+---------+--------------------------+----------------------...
rdehuyss's user avatar
  • 113
1 vote
2 answers
823 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 ...
tony's user avatar
  • 13
0 votes
1 answer
902 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 ...
ColRow's user avatar
  • 43
3 votes
1 answer
13k views

SQL Trying to sum and group by distinct values

I have a table +-------+----------+------------+----------+---------+ | Plant | LineName | WorkCenter | Material | ProdQty | +-------+----------+------------+----------+---------+ | x | xl |...
LRO's user avatar
  • 33
0 votes
0 answers
231 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 ...
Sergio Donati's user avatar
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"."...
Paweł Madej's user avatar
13 votes
2 answers
21k 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,...
Luis's user avatar
  • 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 ...
whitered's user avatar
  • 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 ...
Sam Tresidder's user avatar
7 votes
2 answers
18k 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 ...
oneCoderToRuleThemAll's user avatar
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....
Mehdi Dehghani's user avatar
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 ...
Dex's user avatar
  • 1