100 questions
1
vote
2
answers
78
views
How to get the max amount per day for a month
I have a table with two columns: demo at db<>fiddle
create table your_table("Date","Count")as values
('2022-01-13'::date, 8)
,('2022-01-18'::date, 14)
,('2022-01-25'::...
- 13
0
votes
1
answer
242
views
snowflake dynamic distinct count on a column in view
I have a table with below structure and data. I want to get the distinct count on USER_ID based on WHERE clause filter. I can achieve this with below sample queries.
WITH CTE(REPORT,INSTANCE,ROLE,...
- 171
1
vote
2
answers
169
views
Return only one element per unique ID with only latest joined record
Suppose the following:
create schema bv;
create table bv.user(id bigint primary key);
create table bv.user_photo (
id bigint primary key,
url varchar(255) not null,
user_id bigint references bv....
- 6,551
0
votes
1
answer
48
views
Postgresql - Distinct On Result
I have already used 'distinct on' in query, but the result is still duplicate.
Can i only get two result from below picture? e.g: row 1 & 4 or 2 & 3, two rows is different in id and serial ...
- 331
1
vote
3
answers
63
views
Min dates from group by postgresql
I have table with data:
create table measure_app_scalemeasurement (
store_code text,
certificate_id text,
audit_ending date);
insert into measure_app_scalemeasurement values
('K010','vwv', '10....
- 98
1
vote
2
answers
151
views
Select N latest rows per school, but skip duplicates from the same student
In a Postgres db (engine version 14.6), I have two tables: school_students and id_cards. They look like the following:
CREATE TABLE school_students (
id BIGSERIAL PRIMARY KEY,
school_id ...
0
votes
0
answers
53
views
Is this index for SELECT DISTINCT ON wrong? Why the Seq Scan is still in plans?
I have this table:
CREATE TABLE public.player (
company_id character varying NOT NULL,
id character varying NOT NULL,
created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,...
- 4,095
1
vote
2
answers
712
views
ORDER BY DESC with LIMIT doesn't return the newest rows, unless I ORDER BY twice
I am writing a chat app and want to get a list of recent contacts to show in the sidebar.
My table has the standard chat fields (sender, receiver, msg, date).
I'm currently getting a list of recent ...
- 4,104
1
vote
2
answers
476
views
Using DISTINCT ON for a column of type timestamp to extract the most recent value for each day
I have a table named assets:
create table assets (
id bigint primary key,
name varchar(255) not null,
value ...
- 910
2
votes
2
answers
210
views
DISTINCT ON slow for 300000 rows
I have a table named assets. Here is the ddl:
create table assets (
id bigint primary key,
name varchar(255) not ...
- 910
0
votes
1
answer
47
views
how to apply distinct and group by in django or in postgres?
table production
code
part
qty
process_id
1
21
10
10
1
22
12
10
2
22
15
10
1
21
10
12
1
22
12
12
I have to extract data like based on process, every process has multiple part but I can't take every ...
- 23
1
vote
2
answers
234
views
PostgreSQL select distinct JSONB poor performance
Let's say I have the following PostgreSQL table called products which has millions of records:
CREATE TABLE IF NOT EXISTS mytable (
id serial NOT NULL PRIMARY KEY,
label VARCHAR(50) NOT NULL,
...
- 5,647
6
votes
2
answers
10k
views
PostgreSQL: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
Let's say I have the following PostgreSQL table called products:
CREATE TABLE IF NOT EXISTS mytable (
id serial NOT NULL PRIMARY KEY,
label VARCHAR(50) NOT NULL,
info jsonb NOT NULL,
...
- 5,647
2
votes
1
answer
637
views
SQL left Join with DISTINCT ON returns duplicate rows
I`m using node with mikro-orm v5 and postgres 14.
My schema goes like this:
- Business
- Addresses[]
- Sectors[]
And I want to query the addresses by distance.
The problem is that the return is ...
- 77
6
votes
1
answer
5k
views
What is the BigQuery equivalent of PostgreSQL's `DISTINCT ON`?
I am migrating some queries from PostgreSQL dialect over to BigQuery. One nice pattern in PostgreSQL is DISTINCT ON (key), which returns the first row for every key based on the sequence as defined in ...
- 1,760