Questions tagged [distinct]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
0answers
28 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
33 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
32 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
18 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 ...
1
vote
1answer
48 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
22 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
0answers
43 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
122 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
143 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
22 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
62 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
25 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
45 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
239 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
976 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
69 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
46 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
51 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
56 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
415 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
262 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
62 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
33 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
882 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
345 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
194 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, ...
2
votes
1answer
892 views

Is there a way to speed up a DISTINCT query?

I have a table t in a database (PostgreSQL 10.4): \d t; Table "public.t" Column | Type | Collation | Nullable | Default ----------+------------------------+------...
0
votes
1answer
803 views

Is it ever useful to SELECT DISTINCT on the primary key column(s)?

From my understanding, primary keys are unique by definition. If that's true, would one ever want to SELECT DISTINCT on the primary key? Is this ever practical/useful?
6
votes
1answer
775 views

Why do two identical strings have a different length but the same binary value?

I am trying to return a distinct set of department names from a table - nothing special. However, when using the following query duplicates are displayed: select distinct department_name from ...
0
votes
1answer
76 views

How to generate and insert primary key when using SELECT DISTINCT in OracleDB?

How do I insert (or generate and insert) a primary key when I use the SELECT DISTINCT query? If I have a table made like this: CREATE TABLE testUser( username VARCHAR2(50 CHAR), email ...
1
vote
2answers
577 views

merge 3 mysql tables into one with no duplicates

I would like to merge 3 tables data, each have a number column in common, but all the other data differs. What I would like to achieve is selecting all of the data from the common column and merging ...
1
vote
2answers
264 views

Postgresql: very slow results with “group by”

I am trying fix some severe performance issues on codechecker. Issue reported here: https://github.com/Ericsson/codechecker/issues/1399#issuecomment-368862492 The table is the following: ...
2
votes
1answer
8k views

Counting unique (distinct) users per day

I am new topsql and am interested in counting the number of unique first time users per day. My table only has two columns- user_id (there can be multiple uses by one user per day) and start_time ...
4
votes
1answer
6k views

Calling SELECT DISTINCT on multiple columns

I am unclear on exactly on, SELECT DISTINCT col1, col2, ... FROM table_name When called on one column it gives back only distinct values of that column. What happens when we use it with multiple ...
2
votes
1answer
2k views

Summation of distinct rows - SQL Server

I am pretty sure I am making a basic mistake, but I can't figure it out for the life of me. I am trying to get a simple report of total number of unique employees, number of employees who are male, ...
0
votes
1answer
88 views

Efficient partial DISTINCT ON

I would like to get the most efficient query to give me all the rows that are distinct on one field (ID) but are identical on another field (validity). Let's set a concrete example: you have a table ...
2
votes
3answers
386 views

Display summary results based on empty value in another column

I need some assistance and guidance. I want to retrieve a summary of results based on values in another column. For example, I have a column in my data set that shows an audit date. There will be ...
0
votes
1answer
527 views

Is a UNION query equivalent with or without DISTINCT in subqueries?

Consider the following two queries. SELECT Col1, Col2 FROM TblA UNION SELECT Col1, Col2 FROM TblB and SELECT DISTINCT Col1, Col2 FROM TblA UNION SELECT DISTINCT Col1, Col2 FROM TblB These are ...
1
vote
1answer
8k views

Select unique value pairs that occur (count) more than once inside a table

How can I retrieve an row with unique value pairs that occur more than once... Heres an example of what I'm what I am trying to.. SELECT column_fk_id1, column_fk_id2, COUNT(distinct column_fk_id1, ...
1
vote
1answer
52 views

Conditional Query [closed]

can someone please help me with the below requirement? I have a table with sample data below Now, I want to write a query that 1. It don't miss any ID in the report 2. If a particular ID has a ...
3
votes
1answer
663 views

inconsistent distinct + order by behavior between mysql 5.6 and 5.7

I use MySQL 5.6 and 5.7 on Ubuntu 16.04.3 LTS and I have a problem with the following query (which is derived from drupal 7 but have simplified it in order to pinpoint the issue): select distinct ...
1
vote
1answer
913 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 ...
4
votes
1answer
1k views

performances of distinct/group by

I'm trying to select the most recent authors of messages in a room, using this (simplified) table: Table "public.message" Column | Type | Nullable | Default ...
1
vote
1answer
37 views

Implementing a query, two types of strategies

Tables have not been created yet, but to simplify there is a groups and an items tables. A group contains an item id (and a group id). And several groups may contain the same item id. For instance ...
1
vote
1answer
575 views

select distinct on csv data

A table has CSV data and I want to do a SELECT DISTINCT ID on that table, how should I proceed? +-----------+ | id | +-----------+ | 111,1487 | +-----------+ | 462 | +-----------+ | ...
0
votes
1answer
44 views

MySQL Select all clients that searched for two different items or more

I have a table like this : serial,ip,term 1,1.2.3.4,bag 2,2.3.4.5,shampoo 3,1.2.3.4,handbag 4,3.4.5.6,clothes 5,2.3.4.5,battery 6,4.5.6.7,lamp I want to select all IPs that did search for multiple ...
2
votes
0answers
181 views

Optimize SELECT DISTINCT (with existing index) to avoid sequential scan [duplicate]

I have a records table with ~3 million records. The table has a column called record_type with an index. Table "public.records" Column | ...
0
votes
2answers
1k views

Distinct Children rows ordered by a column of Parent

I've got a query where I want to retrieve distinct Child rows, but ordered by a column of Parent. If I do the following, I get an error because the column specified in the ORDER BY is not included in ...
9
votes
3answers
17k views

DISTINCT on one column and return TOP rows

How do you query for three unique customers with the largest Purchase_Cost? I want to apply the DISTINCT only on Customer_Name, but the query below applies the distinct on all three columns. How ...
0
votes
1answer
95 views

Need to Add Update to Query

A query I'd like to be able to run is one where it looks through all of the maintenance logs of our vehicles, finds those where the part is question was used, and then follow it all back to output the ...