I have customer data with information when the appointment took place, what type of appointment it was and wheather a Product was selected. I am trying to get the earliest appointment for every id where Product is not null. Basically reduce the table where id only appears once with appointment date and type where product is not missing. This doesn’t
Tag: greatest-n-per-group
Postgresql how to select columns where it matches conditions?
I have a table like this: I want to select rows where most recent date with corresponding columns, This is what I want to achieve: I tried to use aggregate but I need inventory_id and customer_id appear at the same time. Is there any method that could do this? Answer Use distinct on: distinct on is a Postgres extension that
Selecting single row if it matches another
I have a scenario of this type: I want to select only one row in such a scenario. How can I achieve this? Answer You could use distinct on with least() and greatest(): An alternative is not exists: Finally: if all of the rows are duplicated, then a simple where condition is sufficient:
Need an SQL query that will left join with another table, which will in turn return the latest values based on time, grouped into a single row
I need help with a SELECT SQL query that will left join with another table on id column, which will in turn return the latest values based on time, grouped into a single row? Basically, join the two tables in such a way that for each record in users table that exists in time_series table, return the latest values based
how to select max score from duplicate mobile_number
how can I filter record 60122839991 with the highest score 95.3758 and return as below without duplicate phone number Answer Use distinct on:
SQL query to get top performer statistics per year (eg 3 pointers)
I have a table with players per year and some stats. For example (player_key, player_name, year, 3point_trials, 3point_score) I want to have as an ouput the top performer per year (3point_score/3point_trials). If two of them have the same % then the breakeven is the the 3point_trials, whoever has the most trials. Output would be per year what is the SQL
Column must appear in group by or aggregate function in nested query
I have the following table. I am trying to query the following: For each year that appears in the Fights table, find the city that held the most fights. For example, if in year 1992, Jersey held more fights than any other city did, you should print out (1992, Jersey) Here’s what I have so far but I keep getting
Select row in group with largest value in particular column postgres
I have a database table which looks like this. I need to group this table by id and select particular row where time_point has a largest value. Result table should look like this: Thanks for help, qwew Answer In Postgres, I would recommend distinct on to solve this top 1 per group problem: However, this does not allow possible to
How to edit my postgreSQL query to select the most recent row by date for several columns
I have a query where I’m trying to select the most recent row based on date. So what I would like my query to do is to select the most recent datapoints for a couple columns in my database. sql = f&…
Find first available value that doesn’t exist
I want to create table for book chapters where pk will be book_id and chapter_internal_number. I’m not sure how find next free chapter_internal_number value for new chapter insert (chapter can be deleted and it’s chapter_internal_number value should be reused). How to find first chapter_internal_number avaiable value for book? Avaiable value is next value that doesn’t exist in ASC order. Table