Questions tagged [distinct]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1
vote
1answer
35 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 ...
0
votes
0answers
7 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 | ...
0
votes
0answers
10 views

Postgresql/rails - Joining on distinct values, or only creating duplicate rows for distinct values [migrated]

I have a number of Practitioners that have Certifications in different States, and they set up their hours in an Availabilities table. They can have multiple certifications in the same state, but I'm ...
3
votes
3answers
218 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 ...
0
votes
1answer
40 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), (...
0
votes
1answer
40 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 ...
0
votes
1answer
30 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 ...
0
votes
2answers
117 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 ...
1
vote
2answers
77 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....
-1
votes
1answer
29 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
votes
1answer
73 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
0answers
34 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
1answer
90 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 ...
1
vote
2answers
69 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: +--------------------------------------+---------+--------------------------+----------------------...
1
vote
2answers
42 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 ...
0
votes
1answer
247 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 ...
1
vote
1answer
611 views

SQL Trying to sum and group by distinct values

I have a table +-------+----------+------------+----------+---------+ | Plant | LineName | WorkCenter | Material | ProdQty | +-------+----------+------------+----------+---------+ | x | xl |...
0
votes
0answers
92 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
0answers
23 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"."...
8
votes
2answers
3k 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,...
0
votes
0answers
21 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 ...
0
votes
1answer
17 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 ...
4
votes
2answers
2k 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
1answer
968 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....
0
votes
0answers
584 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 ...
0
votes
1answer
35 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 ...
0
votes
1answer
68 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
1answer
20 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 ...
2
votes
1answer
495 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 ...
0
votes
1answer
28 views

distinct description

I have the following database: id | remote_id | title | description ------------------------------------- 1 | 12341234 | test | test-description 2 | 4535234 | blah | testestetetst 3 | ...
2
votes
1answer
123 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,...
3
votes
2answers
125 views

LEFT JOIN confusion

I am having issues with this simple scenario. I'm probably missing some basic concept... I have 2 tables, the first of "things" and the second of orders. I've created a sqlfiddle: http://sqlfiddle....
2
votes
2answers
2k views

How to “flatten” multiple rows with same ID (Oracle 11g) by concatenating **multiple** fields per row?

I'm connected to an Oracle Database (11g Release 2 - 11.2.0.4). I'd like to "flatten"/"merge" all rows with the same ID, pretty much as is well delineated here: https://www.w3resource.com/mysql/...
0
votes
1answer
24 views

Building distinct Column from a common column from two tables

Good afternoon team, hope all is well I am hoping you can help me. I have following two tables "GODS.DQ_TOBE" & "GODS.DQ_CURRENT". Within each table there is a common column called "...
1
vote
3answers
220 views

Is there a way to get count based on multiple columns but still have it be distinct in SQL?

I have included two pictures that are in Excel just for ease of viewing but I am doing the queries in SQL. Hopefully, this is possible as the algorithm I have is not giving me the correct results.
0
votes
1answer
26 views

Why this join provide multiple value?

This is my Query: SELECT GROUP_CONCAT(c.categoryName) AS categories FROM post p INNER JOIN join_post_cat jpc USING (postId) INNER JOIN categories c USING (categoryId) WHERE p.postId ...
0
votes
1answer
55 views

How to query results from a huge postgres database?

I am trying to query a few tables in a 125GB Postgres database. I find a hard time getting the results back quickly! Even to find unique rows of a column it takes more than 10 minutes. Either I use a ...
1
vote
1answer
837 views

first and last record in one query

i want to select first and last score from such data WITH t AS ( SELECT 1 user_id, '2019-05-15'::date AS created_at, 4 as score UNION ALL SELECT 1 user_id, '2019-05-13'::date AS created_at, 12 as ...
5
votes
2answers
2k views

DISTINCT column combination with permutations

My postgresql column structure looks like this: id | from | to --------------- 1 | A | B 2 | A | B 3 | C | D Now I want an result which looks like this: res ----- 'A:B' 'B:A' ...
0
votes
1answer
393 views

How to join two columns and find unique combinations

I have the following topological structure (topo) with their types: id(string) | id_from(string) | id_to(string) | type(string) '111' | 'aaa' | 'bbb' | 'type1' '222' ...
0
votes
1answer
67 views

Distinct counts from large table are too slow

I have a table with 1.2 billion rows. The table has 60-70 columns, the vast majority of which are empty. The table is indexed on subscription_id, among other indexes. My query is: SELECT count(...
0
votes
1answer
134 views

Issue with Maximo KPI Returning “0” Instead of a Calculated % [closed]

I am attempting create a KPI in Maximo that will count the work orders completed on time and divide it by the number of work orders due for the current month, then multiply it by 100 to return a ...
1
vote
1answer
71 views

How to (a) rewrite a query that JOINs three tables and (b) make its execution more efficient

I have three tables (A, B and C) with various data regarding mining facilities in them. But only one table (C) has two columns with coordinates. My final goal is to produce a table which pulls similar ...
1
vote
1answer
616 views

Count all rows for each DISTINCT

How can count all rows of WHERE statement: SELECT DISTINCT column1 FROM table COUNT WHERE date::text LIKE '%2014%'; My output: column1 ======= info1 info2 info3 Desired OUTPUT: column1|...
1
vote
1answer
290 views

Remove duplicate comparisons in postgres?

I have a polygon layer named scale_polygons_v4. I want to get ids of overlapping polygons. I have tried the code below: select distinct s1.id,s2.id from scale_polygons_v4 s1 inner join ...
0
votes
1answer
69 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 ...
1
vote
1answer
48 views

Query to partially summarize data in a table using SQL

I'm learning how to handle databases, so this is a trivial question, and quite likely has been answered already. I'm just not familiar to the lingo and have no idea how to search for it (although I ...
1
vote
2answers
1k views

Distinct value across multiple columns

I have a table like this, CREATE TABLE foo ( id integer, col1 integer, col2 integer, col3 integer, col4 integer, col5 integer ); INSERT INTO foo (id, col1, col2, col3, col4, col5) ...
0
votes
1answer
705 views

Combine rows to remove nulls while using latest value based on time

I have a table that has multiple rows associated with each ID but sometimes containing differing info: Key, ID, Name, Title, Time 4, AA, Abe, NULL, 2018-01-05 3, BB, NULL, Mrs, 2018-...
4
votes
5answers
376 views

Select same records that only have different values for a particular column

Below is an example of my customer table. There some records having multiple values in BIRTHDAY DATE (by mistake or so). I only want to select those records that have same values for LASTNAME, ...