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

SQL: how do I select two fields of the same SINGLE row in a group?

I'm using Postgres 9.6.* I have this: street | first_name | last_name 1st | james | bond 1st | mr | q 1st | ms | m 2nd | man | with_golden_gun I would like to get a list of distinct addresses and the first…
JasonGenX
  • 4,952
  • 27
  • 106
  • 198
1
vote
1 answer

Get the 10 records having the most recent related elements

I have two tables: agencies and lessons. An agency can have many lessons. A lesson belongs to one and only one agency. A lesson has a date. I want to get the 10 agencies (or just their ID) having the most recent lessons. In other words I want…
rap-2-h
  • 30,204
  • 37
  • 167
  • 263
1
vote
2 answers

How to efficiently convert the price in SELECT using the existing exchange rate table with given structure?

Situation: I have the exchange rate table like this: date_from cur1 coef1 cur2 coef2 2017-01-01 CZK 27.000000000 EUR 1.000000000 2017-07-03 EUR 1.000000000 CZK 26.150000000 2017-07-03 JPY 100.000000000 CZK …
pepr
  • 20,112
  • 15
  • 76
  • 139
1
vote
1 answer

SQL select latest message of every conversation received AND sent

This question SQL select only rows with max value on a column doesn't solve my problem although it has been marked as duplicate. It assumes my columns from_id and to_id are primary keys, when they don't have such constraint (see code provided…
Hal_9100
  • 773
  • 1
  • 7
  • 17
1
vote
3 answers

MySQL find rows with max value of a field

I have a complex SELECT query which returns info like this: +------+-------+--------+ | a | b | c | +------+-------+--------+ | 2482 | 3681 | 58248 | | 2482 | 17727 | 139249 | | 2482 | 23349 | 170839 | | 3031 | 14877 | 122921 | | 3031…
Neilski
  • 85
  • 10
1
vote
2 answers

Select MAX Value for Each ROW - Oracle Sql

I have one doubt. I need to find what is the latest occurrence for a specific list of Customers, let's say to simplify, I need it for 3 Customers out of 100. I need to check when it was the last time each of them got a bonus. The table would…
Morgan Forever
  • 117
  • 10
1
vote
2 answers

Ordering results in a left join not working

I have two tables, locations and locations_contacts. mysql> describe locations; +----------------+----------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra …
loungehead
  • 55
  • 5
1
vote
1 answer

Symfony - get latest conversation messages

I have a user entity with two user foreign key (from_id and to_id) in a message entity like that : So, all records are : id | from_id | to_id | datemessage | message …
cretthie
  • 349
  • 1
  • 2
  • 11
1
vote
1 answer

doctrine dbal get latest chat message per group

when trying make multiple select $result = $this->qb->select('c.id','c.message','acc.name as chat_from', 'c.chat_to as count') ->addSelect("SELECT * FROM chat ORDER BY date_deliver DESC") ->from($this->table,'c') …
kyty
  • 76
  • 1
  • 9
1
vote
1 answer

MySQL - Convert rows into columns based on latest date and list of ids for each column

I still can't figure this out even after hours of searching and trying. Maybe I did not choose the right words? Okay, so I have table with events like this: EVENT +----+------------+------------+------+ | id | id_article | id_section | date…
David Pocar
  • 23
  • 4
1
vote
2 answers

Select first event after a timestamp per row in another table in PostgreSQL

I have a table with visits to some city by some person on some timestamp: city_visits: person_id city timestamp ----------------------------------------------- 1 Paris 2017-01-01 00:00:00 1 …
Jivan
  • 21,522
  • 15
  • 80
  • 131
1
vote
3 answers

mysql time stamp comparison issue

Hi i had a doubt in mysql query mysql> SELECT * from share5; +----+-------+--------------+--------+-------+---------------------+ | Id | Price | State | Symbol | Trade | created_date …
program sample
  • 7
  • 4
1
vote
1 answer

MySQL Top items based on count within multiple grouping

I am trying to write a SQL query that will return the top items for each company and for each location. I have an example MySQL table (table_x) that looks like this: Date | Company | Location | Item | Price | Quantity | Total_Amount …
Quest
  • 15
  • 3
1
vote
1 answer

Oracle SQL: Get the max record for each duplicate ID in self join table

It's been marked as a duplicate and seems to be explained a bit in the linked questions, but I'm still trying to get the separate DEBIT and CREDIT columns on the same row. I've created a View and I am currently self joining it. I'm trying to get…
spyr0
  • 199
  • 1
  • 2
  • 17
1
vote
1 answer

unable to use LIMIT when using correlated query

I have two tables in Postgres. I want to get the latest 3records data from table. Below is the query: select two.sid as sid, two.sidname as sidname, two.myPercent as mypercent, two.saccur as saccur, one.totalSid as…
user7833845
  • 83
  • 2
  • 11
1 2 3
…
99
100