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

Selecting row from group of row based on a column value

Have following table PkId ProfileId Status Amount 1 234 0 10 2 235 1 100 3 236 0 50 4 236 1 80 5 237 0 70 For row number 3 and 4…
Alex
  • 15
  • 5
1
vote
4 answers

Optimizing slow performing greatest-n-per-group query

I have the following table: id | query | update_date | website_id | device | page | impressions | clicks | position | is_brand ---+---------+-------------+------------+---------+---------+-------------+--------+----------+--------- 1 |…
Frederik Hansen
  • 506
  • 4
  • 21
1
vote
2 answers

Select unique ordered values of several columns in sql

I am using a table with a couple of geometries in each row. I would like that each geometries appears only once in my database. I sorted the couple by distance. I succeded to have distinct geom1 or geom2 but never in the same time. The ids are…
gaspareu
  • 21
  • 2
1
vote
1 answer

Getting around MySql Error 1093 with a NOT IN Subquery

How can I accomplish what I'm trying to accomplish with the following query? DELETE SomeDB.outertable FROM SomeDB.logging AS outertable WHERE outertable.log_id NOT IN ( SELECT SomeDB.f.log_id FROM ( SELECT aa.*,…
tex
  • 2,756
  • 22
  • 31
1
vote
1 answer

Get the top 3 items of a join

I have 3 tables: product, category and product_category (which products are in each category). I'd like to get the top 3 most expensive product in each category. I have this basic relation: select c.name , p.id , p.price from category…
FlamingMoe
  • 2,709
  • 5
  • 39
  • 64
1
vote
3 answers

Get the most recent submission from a team in a submissions table (SQL)

I have a table that can be simplified as: ID |team_id | submission file | date ======================================== 1 | 1756 | final_project.c |2018-06-22 19:00:00 2 | 1923 | asdf.c |2018-06-22 16:00:00 3 | 1756 …
user9979578
1
vote
3 answers

mysql: select the last 10 messages and for each message the last 3 replies

For simplicity lets strip down the messages table to its minimum, with some sample data message_id reply_to createdate 1 0 123 2 0 124 3 0 123 4 1 154 5 1 …
Paul Scheltema
  • 1,993
  • 15
  • 25
1
vote
2 answers

Laravel get last row value form many table in one to Many relationship

I am building an application which has a model with one to many relationship. In the model, the student table has one to many relationship with student address details. I want to retrieve the last row from address details table. I am stuck on how to…
Jivan Bhandari
  • 860
  • 1
  • 10
  • 32
1
vote
3 answers

Laravel Inner join the last value of the right table

I am trying to get the last value of the login log of my user but I can't do it with this code: DB::table('users') ->leftJoin('login_logs', 'login_logs.id', '=', function($query) { $query->select('login_logs.id') …
Jarloyz Juarez
  • 13
  • 5
1
vote
3 answers

get the id based on condition in group by

I'm trying to create a sql query to merge rows where there are equal dates. the idea is to do this based on the highest amount of hours, so that i in the end gets the corresponding id for each date with the highest amount of hours. i've been trying…
Peter Pik
  • 11,023
  • 19
  • 84
  • 142
1
vote
1 answer

Reuse joined tabled in a sub query

Is there any way we can reuse the reference of table we joined in a sub query? I have three tables: task_categories, information about task categories task_priorities, priorities associated with task categories task_links, url links for each…
Vijay Nandwana
  • 2,476
  • 4
  • 25
  • 42
1
vote
2 answers

Find most recent record from "has one through" relationship

I have the following tables: matrices - a matrix name and some other metadata. matrix_values - stores various data points of a 2D matrix as a JSON value column (Postgres). Also the FK matrix_id for a belongs-to association. products - a…
istrasci
  • 1,331
  • 1
  • 18
  • 40
1
vote
3 answers

select id with max date and keep all same max date SQL

I have a sample dataset id category date value 1 a 2013-01-02 7 2 a 2013-01-02 2 3 a 2013-01-01 3 4 b 2013-01-01 1 5 b 2013-01-02 4 6 b 2013-01-03 5 7 c 2013-01-03 …
Rya
  • 329
  • 2
  • 15
1
vote
1 answer

How to link two tables but only take the MAX value from one table in PostgreSQL?

I have two tables exchange_rates TIMESTAMP curr1 curr2 rate 2018-04-01 00:00:00 EUR GBP 0.89 2018-04-01 01:30:00 EUR GBP 0.92 2018-04-01 01:20:00 USD GBP 1.23 and transactions TIMESTAMP …
PyRsquared
  • 6,970
  • 11
  • 50
  • 86
1
vote
2 answers

Laravel distinct on join one to many

I've found a couple of questions online similar to mine, but I can't seem to find a working answer. I have 2 tables USER ID | FIRSTNAME | EMAIL_ADDRESS 1 | Joe Bloggs | Joe@bloggs.com STATUS ID | USER_ID | STATUS | DATE 1 | 1 | 'In' |…
S_R
  • 1,818
  • 4
  • 27
  • 63
1 2 3
…
99 100