Given a postgres table of the following form: group id | timestamp | value ———+——————————-+—— 1 | 2020-04-15 15:04:44.020288+00 | 8.0 2 …
Tag: greatest-n-per-group
Select all the last dates from all list of id
I’m trying to select all the videos that were last inserted, without repeating any ID_video_type (id_video_tipo column). Here’s an example of the data int the database: I’m trying to run the following query but when I do that I get the following result As you can see, I managed to get the vi…
Retrieve a row of data with the most recent date when another row ‘X’ in T-SQL
I have a database of customers who have an effective date and end date of their membership, both separate columns. The data is a bit dirty, however, and a customer can have multiple rows of data, only one of which is their most recent membership record. A member is considered “active” if they have…
SQL View Select Optional
I have a view that the current output something like this The rules are as follows Each group can only have 2 code and always have a default code vs not a default code. IF default code is not null, then always show the row. Regardless if Non default code has value or not If default code is null, then
SQL merge two tables – get only one row for each key value
I’m trying to run an SQL command on my database. I have two tables, one for users, another one for posts. I want to find last post of each user. I tried following: SELECT b.`username`, b.`id`, c.`…
How to write a query that retrieves 3 replies for each user in my users table?
i want to display three replies from each user i have in my users table, so for instance if i have 3 users and each of them had replied to lets say 10 messages, i want my query to only retrieve 9 …
Show Unique values only
I am using SQL Server 2019 and looking for a way to show ONLY the latest value for each AOC based on its latest FW_Version. Here is my query I have so far but this shows everything: What I would like to show ONLY items marked in yellow… How can I make this happen? Here is the list of my
get first row fo each group SQL
I have data like below. I would like to get the top choice for each gender from the following data I have tried mapping the choices by descending order as below : BUT, I would like to get the following table where i get the top choice for eachgender : Answer If you are runing MySQL 8.0, you can use
MySQL Group By for sorted table
I’ve two tables with these structures: Table 1 (comments) Table 2 (posts) My goal is to get a list with the latest comment from each post of a specific category_id. So for example, if I have category 1 as input I need the comments with the id 2 and 1 because these are always the last comments for each p…
How to get top N rows with some conditions
I have a query something like this: But now, I want to see the top 10 product_id for each site and category_id based on the clicks. When I write the LIMIT function, it only shows the top 10 products but it does not group it by category_id and shop_id. How can I do this? Answer Use window functions. You can