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

Join 2 tables and keep only the first closest event

I have the following current tables: table_1 id | timestamp | origin | info table_2 id | timestamp | origin | type My aim is to find, for each line in table 2, the origin event in table 1. I want to keep only the first one. For instance: table 1 1…
Ekans
  • 997
  • 1
  • 8
  • 16
1
vote
1 answer

MySQL Get unique conversation and last messages ordered by date

I'm making a messaging system. I want to retrieve specific user's conversations with content (this user can be both sender or receiver) ordered by date. I looked for every question online but none of them was working as expected. Here is example…
kenarsuleyman
  • 920
  • 1
  • 6
  • 26
1
vote
3 answers

Select all columns but ignore records having duplicate values on some of them

Let's say I have a cars table. It has following columns: id, color, engine, price, upholstery, model_name. So far I've been querying for all cars based on criteria passed from the front end app. However, now I'm told that if multiple cars have the…
Jacka
  • 2,270
  • 4
  • 27
  • 34
1
vote
2 answers

How to display the related records in a single row in Oracle SQL?

I wrote a query joining two tables and I got a below resultset: SELECT emp.employee_id, dept.department_name, dept.department_id FROM employee emp, department dept …
Anitha
  • 77
  • 1
  • 2
  • 9
1
vote
2 answers

What's the best query to get last commented articles?

I have a table comment like : id article_id approved created_at 7 104506 1 2019-12-02 09:36:32 6 104452 0 2019-12-02 09:31:35 5 104498 1 2019-12-02 09:27:56 4 104506 1 …
Guillaume
  • 113
  • 6
1
vote
1 answer

Get top x% rows for every unique value in column by other column value

Table "tags": Source Target Weight #003 blitzkrank 0.83 #003 deutsch 0.7 #003 brammen 0.57 #003 butzfrauen 0.55 #003 solaaaa 0.5 #003 moments 0.3 college scandal 1.15 college prosecutors 0.82 college…
Vega
  • 2,661
  • 5
  • 24
  • 49
1
vote
3 answers

MAX Date for each row

I am having some difficulty at writing a syntax that will provide me with the most recent date for several results. Little bit of background, I am pulling live data from an IBM AS400, I have no SQL tables, I am connecting to the tables via ODBC,…
carchis
  • 25
  • 3
1
vote
2 answers

Using MySQL to SELECT the MAX of a SUM of the last 5 entries?

Hey, I'm not the biggest expert with MySQL but here is what I have so far to get the MAX entery SELECT DISTINCT websites.id, websites.title, websites.url, websites.screenshot, …
Brandon
  • 11
  • 2
1
vote
3 answers

How to write query/create view to limit multiple records to show only max value

Consider the following three tables. A list of contacts, a list of status with a defined "rank" and a join table that links a contact to multiple status's. CREATE TABLE public."Contacts" ( name character varying COLLATE…
mike hennessy
  • 1,359
  • 1
  • 16
  • 35
1
vote
3 answers

Order By One One Column in MSSQL

I Have the following SQL Tables: [Calendar] [CalendarId] [Name] SAMPLE DATA: CalendarId ResourceKey Name 1 1 tk1-Room1 2 2 tk1-Room2 3 3 …
tornup
  • 263
  • 1
  • 4
  • 15
1
vote
3 answers

Is there a way to get first row of a group in postgres based on Max(date)

Input : id name value1 value2 date 1 A 1 1 2019-01-01 1 A 2 2 2019-02-15 1 A 3 3 2019-01-15 1 A …
Anonymous
  • 15
  • 2
1
vote
1 answer

MySQL Query to select row into column with latest created_at date

i am trying to create a query where i want to convert my rows into column with row panel_name GROUP BY and arrange by last created_at of individual date. My data is coming in the column format but not showing it with the date specific. I tried…
user3201500
  • 1,538
  • 3
  • 22
  • 43
1
vote
4 answers

Selecting a column where another column is maximal

I guess this is a standard problem. But I could not find a proper solution yet. I have three columns in table A: ID ID_Version Var 1 1 A 1 2 A 1 3 X 1 4 D 2 1 …
Arne
  • 57
  • 6
1
vote
1 answer

How can I find the item id related to the maximum resource by year in SQL (postgres)?

I'm trying to run a SQL query that seems easy but I don't understand what mechanism I should use to achieve this. I have the following table: | id | playcount | release_date | | -- | --------- | ------------ | | 1 | 47712 | 2019-12-27 | | 2 …
Louison Gitzinger
  • 48
  • 1
  • 8
1
vote
2 answers

Can I merge data from latest and oldest rows grouped by?

I´d like to merge somehow these MySQL 5.6 results. The idea is to get data from latest and oldest rows of each id. Pair time/id values are unique. Table is: | time | id | titulo | precio | vendidos…
Diego F.
  • 13
  • 5
1 2 3
…
99 100