Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

Questions tagged [distinct]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1
vote
1answer
20 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
19 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
38 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
119 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
70 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
20 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
57 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
41 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
114 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
821 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
54 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
32 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
31 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
52 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
285 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
241 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
60 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
32 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
787 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
292 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
167 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
678 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
631 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
561 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
60 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
354 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
199 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
6k 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 ...
3
votes
1answer
5k 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
83 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
364 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
486 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
7k 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
535 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
701 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
989 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
34 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
466 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
148 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
14k 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
93 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 ...
5
votes
2answers
3k views

How to create aggregate function, like sum, with an implicit DISTINCT?

We have an ERP system which allows aggregates to be used (eg SUM(foo)) but not with DISTINCT (eg SUM(DISTINCT foo). Is it possible to create an aggregate function (SUM_DISTINCT), that returns the ...
3
votes
2answers
2k views

Select most recent row by some other column

I'd like to select the most recent row in a MS SQL Server 2008 R2 database based on a couple of factors. I've seen a lot of people want to do the same thing online, and a lot of different proposed ...
3
votes
2answers
783 views

Rank vs. Distinct

Just for curiosity, if I have to use rank to tag distinct values for some columns only, and ignore whatever different values occurred on other columns, why not use distinct and just remove the other ...
4
votes
3answers
633 views

Find values which occur in every row for every distinct value in other column of the same table

I need a select statement which finds values from Column B which occurs in all distinct values for A. Meaning in my example it would return: x since x occurs in every value of A. (1,2,3 and 4) I don'...