Questions tagged [distinct]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1
vote
3answers
47 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
39 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
45 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
694 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
48 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
21 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
47 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
149 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
213 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
56 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
31 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 ...
0
votes
0answers
33 views

How to get distinct documents of user messages to different users?

In MongoDB we have distinct method that returns distinct values of a record from a specific collection: db.inbox.find({"inbox_type": "direct"}).distinct("sender") The above command returns distinct ...
1
vote
2answers
673 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
235 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
139 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
535 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
449 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
355 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
50 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
263 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
172 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
5k 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
4k 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
80 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
341 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
445 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 ...
0
votes
1answer
6k 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
453 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
576 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
767 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 ...
0
votes
1answer
389 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
40 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
137 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 ...
8
votes
3answers
12k 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
2k 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
692 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
580 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'...
0
votes
2answers
3k views

How can I get distinct counts with different where clauses on the same field? [closed]

How can I get DISTINCT row COUNTs with different conditions in a single SELECT statement, and if count is zero get zero as a result? I tried the following query: SELECT DISTINCT A.DeviceFilter, ...
3
votes
1answer
4k views

Postgres: Insert distinct values from one table into another table with constrains in place

I'm trying to insert data from one PostgreSQL 9.3 table to another while removing duplicate entries. I have two tables containing email addresses. The main table contains emails and a tag for each ...
1
vote
1answer
1k views

Using DISTINCT and COUNT in a query

I'm having issues using DISTINCT and COUNT in the same query. I would like to get unique items then get a total count for each month. (I've done this in two queries but I know there's a better way.) ...
0
votes
2answers
976 views

Why is a 'Select distinct limit 100' using tmp tables? [closed]

I hope someone can teach me why MySQL is using temporary tables on a super simple select with a limit of 100 and how to get it do query more efficiently. I'm running MySQL 5.1.53-community. The ...
1
vote
1answer
4k views

Distinct in one column where another column has same values

I have a table sample id | name | type -------+--------+------ PR001 | DC001 | a PR002 | DC001 | a PR003 | DC001 | b PR004 | DC002 | a PR005 | DC003 | a PR006 | DC003 ...
0
votes
2answers
7k views

Distinct column in an inner join query sql [closed]

I have two tables, linked through Artists Firstnames and surnames. One table has the artists BIOG (only one result per Artist.), the other table has the Artist's images (a lot of images per artist). ...