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

How do I pick out one hstore column from a group in Postgres?

Suppose I have a table foo in Postgres 11.5: CREATE TABLE foo (an_int integer, a_varchar varchar(50)); I can select columns unique by integer and grab a couple of the a_varchar values as samples as follows: SELECT an_int, min(a_varchar)…
dfrankow
  • 20,191
  • 41
  • 152
  • 214
1
vote
1 answer

How to get row for every first occurrence of a column value

I have a table named lists having below columns and rows. type | time | alt_id ------------+------+--------- invest_fees| t1 | 5601 invest_fees| t2 | 5601 invest_fees| t3 | 5601 countries | t4 | 5601 invest_fees| t5 …
sk17261205
  • 421
  • 1
  • 5
  • 12
1
vote
3 answers

How to select all columns and count from a table?

I'm trying to select all columns in table top_teams_team as well as get a count of values for the hash_value column. The sql statement here is partially working in that it returns two columns, hash_value and total. I still want it to give me all the…
Seventh
  • 23
  • 3
1
vote
3 answers

Avoiding the “n+1 selects” problem when I want a subset of related resources

Imagine I'm designing a multi-user blog and I have user, post, and comment tables with the obvious meanings. On the main page, I want to show the ten most recent posts along with all their related comments. The naive approach would be to SELECT the…
cyco130
  • 4,654
  • 25
  • 34
1
vote
4 answers

To select text value from a group in sql

I want to select the row if that particular string is present in that group I have a table which contains id and detail column. Query to create table: create table emp_detail (id number,details varchar2(20)); insert into emp_detail…
richirich
  • 11
  • 1
1
vote
3 answers

How to use MAX from single column from single table while querying multiple columns from multiple tables

So I got a request for some data from someone in my company. This data request requires SQL query of multiple columns from multiple tables BUT can only be the most recent incident from a column from one of those tables. Here is the kicker...each…
user2079024
  • 161
  • 2
  • 8
1
vote
2 answers

Group and Select First From Two Tables Linq

I'm trying to create a simple query using EFCore, returning the list of people i'm conversing with, and the last message that was sent between the two of us (pretty much like how it's displayed on Facebook Messenger or Whatsapp). I created the linq…
Yehia A.Salam
  • 1,987
  • 7
  • 44
  • 93
1
vote
5 answers

How to show the newest post only

How can i show a list including the field M.hID, but only the newest M.hID depending on M.tDate? SELECT T.kId, T.tKode, M.hId, MAX(M.tDate) as newestTDate FROM _PFaq T JOIN _mUser M ON T.kId = M.kId AND T.tKode = M.tKode group by T.kId, T.tKode,…
lynge
  • 13
  • 2
1
vote
2 answers

How to get maximum value from two tables where only IDs overlapping?

I have two different tables with headers (shortened) table a supplier_id, company_name table b product_id, product_name, supplier_id, unit_price I have to produce a table with only maximum unit price products of each company. The header of the…
turo62
  • 35
  • 3
1
vote
2 answers

Get combined row from multiple tables

I have a main table and some sub-tables that contain at least 1 column from the main table. Sub-tables are updates of some part of main table. I would like to get updated row of main table at a specific date. Main table: table1 | id | colA | colB |…
Otgon-Erdene Munkhbaatar
  • 13
  • 3
1
vote
1 answer

Query for most recent record per group

I have a table name price: CREATE TABLE price ( price_id bigint pk, product_id bigint, start_date timestamp with timezone ) with the query: select price_id, product_id, price, start_date from price where start_date <= current_timestamp I…
RukaDo
  • 155
  • 1
  • 9
1
vote
3 answers

oracle sql command for finding TOP 10 and LEAST 10 salaried from EMPLOYEES TABLE

I like to find TOP 10 and LEAST 10 salary of employees. The output should show the TOP 10 and LEAST 10 salaried employees together I got the TOP 10 salary employees in one sql command and found LEAST 10 salary employees in another sql command. how…
Syed Ahmed
  • 47
  • 1
  • 2
1
vote
1 answer

How to get the most recent document per product?

I have the following simplified tables: Product: [uniqueId|name] ProductAttachment: [FK productId|FK attachmentId] Attachment: [uniqueId|createdOn|name] The product and attachment tables are in an n:m relationship with productAttachment being the…
Rapnnex
  • 13
  • 3
1
vote
2 answers

SQL select unique column with maximum for 2nd column

Please how to combine maximum and distinct to have always unique Id and maximum of the column for that unique Id? For example, if I have, OrdinaceId Priloha2Id 5 1 5 2 6 2 6 4 7 1 result will…
Bobek
  • 757
  • 3
  • 7
  • 15
1
vote
1 answer

How to select the most recent 8 lines from a table for unique serial numbers in the table

I made automated test GUI's using labview that test our 8 channel devices. Each time a test is completed, 8 rows are automatically added to a SQL Server table, and all of these 8 rows have the same Serial number recorded. Every time a test is…
Mattias Payne
  • 23
  • 3
1 2 3
…
99 100