I have a query like: SELECT DISTINCT ON (services.group_id) services.service_id, ... FROM services WHERE services.group_id IN ('67240181b97477f054f9b1bc', '67240181b97477f054f9b1be') I have an index on group_id. Why does Postgres need to do an extra heap scan? I just want any 1st match…
I'm working RWFD emergency room dataset, which includes patient's race and what department they were referred to. I'm trying to show which department each race (7 total) is most commonly referred to, with the stipulation that department_referral is not 'None'.…
I want to display the count of hosts/servers that do not have a given package installed (example: "abc") in a Grafana dashboard with Postgres as data source. Example table of a single server with "abc" package present which is displayed…
I'm working on a NestJS application with TypeORM and PostgreSQL, and I need to fetch a list of users sorted by the timestamp of the last message they sent. I have two entities, User and Message, defined as follows: user.entity.ts…
Table tmch contains thousands of rows here it is: CREATE TABLE IF NOT EXISTS public.tmch ( id bigserial NOT NULL, year integer, week integer, my_number integer, device_id bigint, CONSTRAINT tmch_pkey PRIMARY KEY (id) ) Sample data: 1716446 2024 37 13…
Suppose I have the following data, and I want to know for each person what was their highest performing period, breaking ties in favor of more recent periods. I would like to see ONLY the following rows: 5 (Alice's best…
I have two related tables as below. Master table: ID Date Rev 1 2022-01-01 1 2 2022-01-02 1 3 2022-01-02 2 4 2022-01-03 1 Detail table: ID hour Capacity 1 1 1 1 2 2 1 3 3 1 4…
I have a very flat table that is a collection of swimmer names, distances, events (strokes), and times(float). What I would like is to get the only fastest time for each combination of distance and stroke. To be clear, there…
uid created_date user_id 1 2023-07-05 05:50:00.000 1 2 2023-07-05 06:50:00.000 1 3 2023-07-05 06:50:00.000 1 4 2023-07-05 06:40:46.000 2 5 2023-07-05 06:57:46.000 2 6 2023-07-05 06:43:46.000 2 For example, above is the data. I am looking for output to have…
For each row in table line there can be many rows in table line_version. I need to find only N (let's say 2) "latest" rows for each line row. Latest here means the largest IDs. For example, that's how line_version…