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
2 answers

Grouping in T-SQL and choosing the longest name

I am using SQL Server 2012. I was trying to resolve a "typical" grouping problem. But I couldn't resolve this correctly. Imagine that you have this table: I would like to group by position. It could be easy: select position, COUNT(*) from…
jorge_vicente
  • 358
  • 1
  • 5
  • 15
1
vote
1 answer

MYSQL GROUP BY with WHERE CLAUSE

I've looked through the greatest-n-group-by group and can't find this answer and can't make the JOIN solutions work with the WHERE CLAUSE. Here's what I've got. t1 table: ID Product_Name Quantity pharmacyShort Copay_Amount 581284 …
Dave Smith
  • 13
  • 1
  • 5
1
vote
3 answers

Select distinct values from a column based on highest value in another column

I have a table in which I need to return a distinct value for each id based on the highest reference value. As an example, this table: my table |--------|----------|-------------| | ID | Amount | Ref_value …
JD Gamboa
  • 362
  • 4
  • 20
1
vote
1 answer

SQL latest record per foreign_key

I've got the following 2 tables: ingredients (id, name) ingredient_prices (id, ingredient_id, price, created_at) such that one ingredient can have many prices. What will be the best way to get the latest entered price per ingredient? I know it can…
Nikhil Gupte
  • 3,266
  • 4
  • 25
  • 15
1
vote
3 answers

How to use subquery in outer join condition

My database have customers and orders. Customer may have many orders with different primary keys and creation dates. I want to make a view for customers, which consist of each customer joined with their latest order (or null if customer doesn't have…
Tuomas Toivonen
  • 21,690
  • 47
  • 129
  • 225
1
vote
1 answer

How to get top 150 results based on a column data

I have a TEMP_TABLE with the below data SOURCE - Number DESTINATION- Number COUNT- Number (Total count of transactions) SUM- Number (Total sum of transactions) Sample data: SOURCE DESTINATION COUNT SUM 123123123 99009900 65 …
Sai Avinash
  • 287
  • 4
  • 7
  • 17
1
vote
1 answer

rank between dates

I have a table that holds historical information, below. TableA ID Set_Date Reason pk 1 1/1/17 denied 1 1 1/2/17 approved 2 TableB StartDate EndDate ID 1/1/17 1/2/17 1 I need the latest value when the…
John
  • 289
  • 3
  • 14
1
vote
1 answer

MySQL: Optimize greatest row in group query

I have table Addresses with following schema: CREATE TABLE `Address` ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, ID_Person INT NOT NULL, Street VARCHAR(50), HouseNumber VARCHAR(10), City VARCHAR(50), Zipcode CHAR(5), …
Zbynek
  • 5,673
  • 6
  • 30
  • 52
1
vote
3 answers

Min value from Database in MySQL

Am trying to find the min value from past 30 days, in my table there is one entry for every day, am using this query SELECT MIN(low), date, low FROM historical_data WHERE name = 'bitcoin' ORDER BY STR_TO_DATE(date,'%d-%m-%Y') DESC LIMIT 7 But…
Sara Tariq
  • 27
  • 6
1
vote
4 answers

using distinct doesn't work in sql server

For some reason the following DISTINCT doesn't work. I was expecting only one b@gmail.com from Email column, but I get two identical ones. 1> SELECT DISTINCT t.Email,t.Lat_From,t.Lon_From FROM tbl_Trip t,tbl_User u WHERE t.Email like…
Bruce
  • 2,133
  • 12
  • 34
  • 52
1
vote
1 answer

Laravel Eloquent group by most recent record

I'm trying to get the most recent record for a single customer on a table. Example: ID Customer City Amount 1 Cust001 City1 2 2 Cust001 City2 3 3 Cust001 City1 1 4 Cust001 City2 1 5 Cust001 …
TJ is too short
  • 827
  • 3
  • 15
  • 35
1
vote
3 answers

SQL to get the third highest ID from the table with a particular type of column value

I have a table say "Packages". packageid FlightType 230 Common 231 B717 232 A330 233 B717 234 Common 235 B767 236 A330 237 A330 238 …
rkm
  • 13
  • 1
  • 6
1
vote
2 answers

Problem selecting max year, max month & max date in one query

CREATE TABLE TEMP ( C_INVOICE_ID NUMBER(10), DIA NUMBER, MES NUMBER, ANO NUMBER, SOCIO_NEGOCIO NVARCHAR2(60) NOT NULL, PRODUCTO_NOM NVARCHAR2(60) NOT NULL, M_PRODUCT_ID NUMBER(10), CATEGORIA NVARCHAR2(60) NOT NULL, COSTO NUMBER ) INSERT INTO TEMP…
Jose Luis Rodriguez Salas
  • 65
  • 11
1
vote
1 answer

Group by with MAX in SQL

I am trying to retrieve the max(CRN) with a group by function to retrieve duplicate records aside from CRN but it is not working: below is my select statement: select max(CRN), TERM, SUBJECT, COURSE, SCH, R_KEY, R_PERC from database …
jean
  • 89
  • 1
  • 7
1
vote
2 answers

SQL - Select top n grouped by multiple fields, ordered by count

I am attempting to find the top n records when grouped by multiple attributes. I believe it is related to this problem, but I am having difficulty adapting the solution described to my situation. To simplify, I have a table with columns (did is…
Cyruno
  • 13
  • 4
1 2 3
…
99 100