Stack Overflow
Stack Overflow

Questions tagged [greatest-n-per-group]

Query the row with the greatest/least value per group.

One of the most frequent stumpers for SQL programmers is to query rows that match the greatest (or least) value over a given group. For example, all details about the City with the largest population per state. You can't do it simply with GROUP BY because the other columns will be ambiguous.

A variation that appears occasionally is to query the top n rows from each group (see the ).

Make sure to include the database tags as well (, , etc.) because depending on the database, the optimal query might be different.

4224 questions
1
vote
1 answer

how to take first value by rank or second in oracle select

I made ranking by using rank() OVER (PARTITION BY ? ORDER BY ? ASC) funtion. It is working fine. But I need to take 1st falue if max rnk of particular application_id value is 1 otherwise 2nd one. TABLE is like this APPLICATION_ID | EMP_CODE…
Abdusoli
  • 661
  • 1
  • 8
  • 24
1
vote
1 answer

Grab the correct set of data from database Mysql/MariaDB

Consider the following data from a table SELECT * FROM messages; +----+------------+------------+----------------------------------------------------------------+------------+ | id | did_from | did_to | message …
Deathcriw
  • 121
  • 12
1
vote
3 answers

mysql limit rows per user in login log table

I have a mysql table with 1 row per user login to our site. I only want to keep the last 10 or so logins for each user. Some users login every day; others only once a month. So I can't just delete rows older than a certain amount of time. How can I…
Mike Scanlin
  • 61
  • 1
  • 4
1
vote
2 answers

Looking for SQL-Server function to identify min/max row and extract information from these rows

Imagine data in this format: Customer, Object, Price [William, Orange, 100p William, Apple, 80p William, Banana 60p Casper, Cola, 150p Casper, Beer, 120p Casper, Peanuts, 200p] I am interested in extracting the most and least expensive purchase for…
Blackrunner01
  • 23
  • 4
1
vote
2 answers

How can I SELECT a row with MAX(value) from a derived table where the values are all calculated sums?

I have tried the following code but I just get the full table with all the names and sum values instead of one row with the max value: SELECT stageName, max(total_salary) FROM ( SELECT c.*, sum(p.dailySalary) as total_salary from…
cuffty
  • 21
  • 5
1
vote
1 answer

Getting the first 5 column of a group

I have a SQL where if i run this: SELECT msg.message_id, u.user_id,u.color, c.comment_id FROM (SELECT * FROM messages WHERE receiver_id = 17 ORDER BY date DESC LIMIT 5 OFFSET 0) msg INNER JOIN comment c ON c.message_id=msg.message_id INNER JOIN…
user12990369
1
vote
3 answers

Select rows that has the same value in one column and different in other

I have a table with data of multiple versions. Each row has an identifier and a version. I need to select the rows with the higher version, but not each rows have the same version. For example: str_identifier | version |…
Daniel Blanco
  • 97
  • 1
  • 8
1
vote
1 answer

Count of top 10 products bought?

I am having trouble getting the the count of the top 10 products bought. I am using MySQL 8. I was given a table that includes the following column: Product - Speakers, E book reader Shoes, Sunglasses Sports Magazine, Car…
hibe1927
  • 11
  • 1
1
vote
2 answers

How to remove non exact duplicates in SQL Server

Currently I can get data that is from each report and filtered by case type and again on case open and for each casereport that I want. However as a case can be open over several months I want Only want the first month it appears. for instance a…
calcal
  • 13
  • 2
1
vote
2 answers

Finding top 25 % customers for each group using T-SQL

I have a following temp table Customer Customer Group Price A Sales 100 B Lease 200 C Lease 300 D Lease 50 E Lease 100 F Sales …
Venkatesh Malhotra
  • 261
  • 1
  • 3
  • 14
1
vote
2 answers

Select records according to data's priority of a column - mysql

connections_tbl point1 point2 medium ktm pkr air pkr ktm road pkr baglung road baglung palpa road ktm brt air brt ktm road baglung pkr train required output point1 point2 medium ktm …
Prajwal Siwakoti
  • 23
  • 6
1
vote
2 answers

How to get latest date of specific record without specifying record?

I have 2 tables: order and transportation. ORDER id TRANSPORTATION id order_id date status (could be 'ok', 'ca', 'ko') 1 order can have more than 1 transportation. I want all orders which its latest transportation status is…
User1
  • 127
  • 1
  • 14
1
vote
2 answers

How to get latest Date AND TotalEnrollments from a list with Duplicated values

I have a list of schools. There are duplicated DISTSCH, I need the row that brings me the latest week WITH/AND the corresponding totalenrollmentsEQ. select DATACOLLECTIONWEEK, districtname, DISTSCH,totalenrollmentsEQ from DB.SCHEMA.TEST where…
Hakka-4
  • 87
  • 8
1
vote
2 answers

Query Postgres in two table

I need to bring the result of a consultation. I need to bring the latest version of each inst_code, as well as its name (table B)
rbrt
  • 79
  • 2
  • 10
1
vote
1 answer

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: SELECT DISTINCT dbo.Model.ModelName AS AOC, …
peka
  • 51
  • 1
  • 7
1 2 3
…
99 100