Questions tagged [distinct]
The distinct tag has no usage guidance.
14
questions
28
votes
5
answers
89k
views
SELECT DISTINCT on multiple columns
Supposing we have a table with four columns (a,b,c,d) of the same data type.
Is it possible to select all distinct values within the data in the columns and return them as a single column or do I ...
- 1,940
2
votes
1
answer
1k
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 ...
- 251
8
votes
2
answers
14k
views
Get the last 5 distinct values for each ID
I'm working with PostgreSQL 9.4.
I have a table that contains the following entries:
id | postcode | date_created
---+----------+-----------------
14 | al2 2qp | 2015-09-23 14:46:57
14 | al2 ...
- 95
3
votes
2
answers
6k
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 ...
1
vote
1
answer
24k
views
2 GROUP BY WITH DISTINCT , SUM , COUNT : PHP MYQL
Ref : https://stackoverflow.com/questions/32066097/group-by-with-having-distinct-php-mysql
I got answer (thanks coytech ) but i need one more column in its :
as per :
id | mid | pid | owgh | nwgh |
...
- 123
13
votes
2
answers
20k
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,...
- 317
6
votes
1
answer
2k
views
Fastest query for distinct IDs in a many-to-many relationship
I have this table in PostgreSQL 9.4:
CREATE TABLE user_operations(
id SERIAL PRIMARY KEY,
operation_id integer,
user_id integer )
The table consists of ~1000-2000 different operations ...
- 1,195
5
votes
1
answer
1k
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 ...
- 153
3
votes
2
answers
2k
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 ...
- 859
2
votes
1
answer
361
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,...
- 1,456
2
votes
1
answer
7k
views
Grouping based on the status, then count its product and the total based on its category ID
I have a table with product ID, category ID and status of the product.
+---------+-------------+-------------+
| id | cat_id | status |
+---------+-------------+-------------+
| 1 ...
- 159
1
vote
2
answers
755
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 ...
- 13
1
vote
1
answer
62
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 ...
- 123
0
votes
0
answers
116
views
Retrieving a record for the last date received and returning the value of another column in that row [duplicate]
I have the following query which is returning me total number of expected records :
select a.New_FirstName + ' ' + a.New_LastName as 'Account Name', a.New_MPRNNumber,MAX(b.New_readdate) as 'Last SC ...
- 163