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

query that for each position bring the id of the employee with the lowest salary

need to create a query in Oracle's SQL that for each JOB, return the employee ID with the lowest SALARY. I tried like this, but it brought me all SALARIES and ID: SELECT JOB, MIN(SAL) FROM EMP GROUP BY EMPNO, JOB; And so it brings me only the…
rdevenz
  • 29
  • 5
1
vote
1 answer

SQL : Get the 3 first occurrences of a field

I have a PostgreSQL table with 2 fields like the following. Field A is the primary key. A | B ------ 1 | 1 2 | 1 3 | 1 4 | 1 5 | 2 6 | 2 7 | 2 8 | 2 9 | 2 10 | 3 11 | 3 I'm looking for a request to get only the 3 first occurrences of B,…
Florian Guéniot
  • 21
  • 1
1
vote
2 answers

get first row fo each group SQL

I have data like below. I would like to get the top choice for each gender from the following data subjectID <- c("1", "2", "1", "0", "1", "0", "0", "1", "0", "2", "0", "0", "2", "2","2","1","2","1","0","2") gender <- c("M", "M", "F",…
R noob
  • 495
  • 3
  • 20
1
vote
1 answer

How to get top N rows with some conditions

I have a query something like this: SELECT product_id, site, category_id, session_time, sum(cast(coalesce("#clicks", 0) AS bigint)) AS clicks FROM df WHERE site IN ('com', 'co') AND session_time =…
datazang
  • 989
  • 1
  • 7
  • 20
1
vote
2 answers

Group By ignores sorting in subquery

There is a TLDR version at the bottom. Note: I have based my current solution on the proposed solution in this question here (proposed in the question text itself), however it does not work for me even if it works for that person. So I'm not sure…
findusl
  • 2,454
  • 8
  • 32
  • 51
1
vote
2 answers

Choosing which line to display based on a set of criteria

I am doing continuing development on a project that is giving me a lot of trouble. First off, I do not know how to use Entity Framework, but I know database design, and looking through this database is giving me headaches because it is not…
Mike Payne
  • 87
  • 2
  • 10
1
vote
2 answers

How to get only one record for the minimum wage employee from each department? DB2

I barely just started on database languages, so I do not know anything about JOIN, PARTITION, ETC. but this is what I have so far. What I tried: SELECT MIN(SALARY) AS "MIN SALARY", WORKING.DID, ENAME FROM DEPARTMENT, EMPLOYEE, WORKING WHERE…
Martin Tran
  • 7
  • 3
1
vote
2 answers

Writing Queries without the limit clause

How can I rewrite the below query without the Limit clause? SELECT project.title, COUNT(project2manager.managerid) AS Cnt_Manager FROM project2manager JOIN project ON project2manager.projectid = project.projectid GROUP BY…
jake jone
  • 35
  • 4
1
vote
3 answers

Nth salary in SQL

I'm trying to understand below query, how its working. SELECT * FROM Employee Emp1 WHERE (N-1) = ( SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary …
Maria
  • 452
  • 1
  • 6
  • 20
1
vote
3 answers

Calculate maximum of column values with common value in another column in PostgreSQL

I am trying to calculalate maximum of column values with common id. I have following table as input TABLE 1: | id | seq | score | | ----- | ------ | ----- | | UA502 | qrst | 8.2 | | UA502 | abcdef | 2.2 | | UA504 | yzab | 8.8 | |…
rshar
  • 1,381
  • 10
  • 28
1
vote
2 answers

How can I return only one record per type?

I have a table with multiple records per one account type and I want to return only one by latest date. I have: SELECT id_nbr AS ID, contact_type AS contype, last_update AS date FROM table WHERE (contact_type = 'AAA' OR contact_type = 'BBB' OR…
JCP
  • 169
  • 1
  • 7
1
vote
1 answer

SQL get single row based on multiple condition after group by

Need help with creating query for below case : Suppose I have a Table with following records Name Date Time Category CategoryKey John 10/20/2012 10:00 Low 2 Sam 10/20/2012 10:00 High 4 Harry 10/20/2012 …
SlashMet
  • 13
  • 5
1
vote
1 answer

How to SELECT post entries to which latest comment were created with SQL?

I have tables post and comment that has foreign key(post_id) to post. I want to get 100 "bump ordered" post entries. The post entry to which latest comment entry was created comes first. My first attempt was: SELECT * FROM post WHERE id IN ( …
Robert C. Holland
  • 1,651
  • 4
  • 24
  • 57
1
vote
1 answer

Oracle. Return correct record based on fields values

From the previous post, someone was already helpful and helped me out to create this select which returns 1 record by max. festpae_id SELECT * FROM ( SELECT discount, osp_id, sero_id, estpt_id, ROW_NUMBER() OVER …
Viktor
  • 323
  • 4
  • 15
1
vote
3 answers

Get lastest version of a content group SQL

I have content table where I have many contents and various versions of those contents. The columns are content varchar,document_id int (identifies a content between many versions) and version int I want to return the lastest version of each content…
dextervip
  • 4,999
  • 16
  • 65
  • 93
1 2 3
…
99 100