I have a table which requires filtering based on the dates. The goal is to select the latest date when there is multiple data points like in the example above. The account 3000 in the dataframe occurs under two Groups but the up-to-date and correct result should only reflect the group X because it was ingested into Databricks very recently.
Tag: greatest-n-per-group
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 contacts by getting a list of messages with distinct (sender, receiver) pair: The result returned is wrong as it doesn’t contain the
PostgreSQL – I want to query out the latest Subscription status for every client with the date
The client can opt-in or opt-out for subscription from time to time. I need the latest Status of every client with the subscription date. If the client has opted-in at the first instance and never opted-out again, I need the first date when they opted-in. However, if the same client has opted-out after opting-in and then opted-in again, I need
How to update only one of duplicated rows in SQL without primary keys?
I have a table that have the following columns: st_id sbj_id desc scr sbm_dt 2001 10 Q2 7.4 2021-05-03 17:03:32 2001 10 Q1 8.0 2021-04-03 18:07:35 2011 10 Q1 5.0 2021-04-03 19:07:35 2001 10 Q2 7.4 2021-05-03 17:03:32 I want to update the st_id value on the last row of the table to 2011. How can I update only one
LEFT JOIN to return only first row
I am making a join with two tables, tab_usuarios (users) and tab_enderecos (address). tab_usuarios structure: id_usuario nome usuario 1 Administrador admin 2 Novo Usuário teste 3 Joao Silva jao tab_enderecos structure: id_endereco id_usuario cidade uf 2 1 cidade SP 20 2 Lorena SP 22 2 Lorena SP 24 3 Campinas SP 28 4 Lorena SP I have this simple query
DISTINCT nor GROUP BY doesn’t work to return unique values
I need only latest updated location from each barcode but I can’t achieve that. Please help I am a newbie. Answer I think you can do that by using Row_number() like this:
sql select everything with maximum date (that that is smaller than a specific date) without subqueries
I would like to write a sql query where I choose all rows grouped by id where the column date is the latest date for this id but still smaller than for example 16-JUL-2021. I would like to do this without using subqueries (in oracle), is that possible? I tried the below but it doesn’t work. Answer You can find
UNION ALL Slower than N queries
This question is following this question where I wanted to select the MAX value of multiples fields while retrieving each row. The accepted answer with UNION ALL worked like a charm but I now have some scaling issues. To give some context, I have more than 3 million rows in my matches table and the filters used in the WHERE
Get last and first record using rank()
I need to get first and last record (ordered by Date column) from table for certain SSID. It is not a problem if there is more records with same max or min date. All I need is union all. I am getting last record having max(date) with: How to I get first record (min(Date)) as well (same thing only with
Select the row with the most recent modified date
I have this table: user_id name email modified_date 1 John a@gmail.com 2022-01-01 1 John b@gmail.com 2022-01-02 1 Lucy c@gmail.com 2022-01-03 2 Joey d@gmail.com 2021-12-24 3 Mike e@gmail.com 2022-01-01 3 Mary f@gmail.com 2022-01-02 I’m trying to get unique user_id’s email with the most recent modified_date. This is my expected output: user_id email name 1 c@gmail.com Lucy 2 d@gmail.com Joey 3 f@gmail.com