i have 2 columns in a table. Data looks like this The output should give the folio_no along with the flag which occured maximum number of times for that particular folio number. i tried doing the below but it throws an error select folio_no, max(count(flag)) from table group by folio_no; Answer We can use an aggregation: Note that I assume
Tag: greatest-n-per-group
Select the Max row number for an account
I need to only pull the max row number for an account. I know it’s a grouping issue. Current data: ACCOUNT_UID ID NAME ACADEMIC_PERIOD CAT_BY_DATE CAT_DATE MAX_ROW abc abc Popeye 202190 CPT 9/15/2021 1 abc abc Popeye 202190 CSH 10/4/2021 2 I only need the second row. Current query: Answer You can try the following: The idea is to apply
Using GROUP BY and JOIN-ing non-aggregated columns to query
I know there are many similar questions, but I haven’t managed to solve my problem by reading them. I’d appreciate some pointers. Here’s some example data from my dummy table below: id foo bar baz moo ins_date percentage yes no maybe 38 foothing bar_one pizazz amoosing 2018-05-26 06:59:00 81 25 529 196 41 foothing bar_one pizazz amoosing 2018-05-29 06:43:00 83
Return row per key based on precedence in Oracle’s SQL
I have data as below. Some items have more then one type id. I need to select only one from each id. My condition is that if exists (type id of item == 1) select only that row; if not exists type id is 1 check if exists (type id of item == 2) select that row; if not exists
Effectively select latest row for each group in a very large table?
I have (for example’s sake) a table Users (user_id, status, timestamp, …). I also have another table SpecialUsers (user_id, …). I need to show each special user’s latest status. The problem is that the Users table is VERY, VERY LARGE (more than 50 Billion rows). Most of the solutions in for instance this question just hang or get “disk full”
Max from joined table based on value from first table
I have 2 tables. First holds job details, second one the history of those job runs. First one also contains job period, per customer which is minimum time to wait before running next job for same customer. The time comparison needs to happen on started_on field of second table. I need to find out the job ids to run next.
Select 2 products per city with most counts in PostgreSQL
I have this dataset: I want the top 2 counts per the_city, so the expected result should be: I have tried this but it’s wrong Answer It’s greatest-n-per-group problem. You can use row_number()over() window function to to serialized city wise product list in descending order of count(*). Then select first two rows from each city. Query: Output: the_city the_product product_count
Limit the results returned by MySQL join to 1
I’m doing a JOIN on two tables and the right side has multiple results for the ON clause: e.ID = ti.event_id. How do I limit the results returned by the join to 1? Sort of like LIMIT 1 events_detail …
Filter data based on group SQL Netezza
Please I have the below table, I need to filter the data based on the DATE column, In the Sus_ID level if the DATE column has NULL get this row or if the hasn’t NULL record will get the row for the newest DATE the desired result should be the below I tried the below code but it didn’t work
How to show the most recent value for each row [closed]
Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed 1 year ago. Improve this question This is my current query. I used an inline view to only show the top 5 results for the companies with the highest volume of