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

select the last study (max) course according the user

What I am doing? I am displaying the user according to there studied. I want to fetch the last course studied by each user. Problem? I am not getting the last course name according to last studied course. I am getting the first course name.(java)…
always-a-learner
  • 3,671
  • 10
  • 41
  • 81
1
vote
4 answers

Sql Query distinct select

My table: 0010121301 000000000001000057 TULUMBA KG KG 1020 20170911 9.000 0010121302 000000000001000057 TULUMBA KG KG 1020 20170911 30.000 0010121303 000000000001000057 TULUMBA KG KG 1020 20170911 10.000 0010121304 …
Şükrü Kaya
  • 11
  • 1
1
vote
4 answers

I'm trying to load only the last 3 comments on every post

i want get all posts with last three comment on each post. my relation is public function comments() { return $this->hasMany('App\Commentpostfeed','post_id')->take(3); } This would return only 3 comments total whenever I called it instead of 3…
Ashok
  • 37
  • 7
1
vote
2 answers

SELECT and GROUP BY

I just want to filter few columns from the table according to MAX(ts). ts = timestamp. And everything is OK if I select only two columns - deviceid and ts: SELECT deviceid, MAX(ts) FROM device_data GROUP BY deviceid and the result: but I also…
Damian Kowalewski
  • 11
  • 5
1
vote
0 answers

Get rank of contestants from points table with additional and nested conditions

We're trying to calculate the rank of contestants for a specific contest, using the following select query. The GROUP_CONCAT workaround is actually a solution that was offered here on SO for a similar question. However, as we added more conditions…
Emad Omar
  • 729
  • 9
  • 23
1
vote
2 answers

Concatenation with FILTER clause

This question follows this one. I now have this code: select min(purchaseDate) filter (where fruitType = 'apple') as appleFirstPurchaseDate, min(purchaseDate) filter (where fruitType = 'orange') as orangeFirstPurchaseDate, customer from…
Guillaume
  • 168
  • 2
  • 14
1
vote
1 answer

Selecting max value from nested query

I have a nested query which returns the totalFare of all passengers grouped by the different passenger types. SELECT PASSNGR.type , sum(FARE.fee) AS totalFare FROM Passenger AS PASSNGR JOIN Fare AS FARE ON (PASSNGR.type = FARE.type) GROUP…
3rdeye7
  • 536
  • 4
  • 25
1
vote
2 answers

Delete all but greatest-n-per-group in Postgres

If I have two Postgres tables, say CREATE TABLE store ( id text, name text ) CREATE TABLE product ( store_id text, id text, created_at timestamp ) I know I can get a table with the newest product with SELECT product.* FROM…
Andy Haskell
  • 677
  • 5
  • 16
1
vote
1 answer

Select max, with tiebreaker

I have a road_insp table: create table road_insp ( insp_id integer, road_id integer, insp_date date, condition number, insp_length number ); --Run each insert statement, one at a time. INSERT INTO road_insp (insp_id, road_id,…
User1974
  • 276
  • 1
  • 17
  • 63
1
vote
2 answers

How I can get Second max salary using "over(partition by)" in oracle SQL?

I already get it by doing this query: SELECT * FROM ( SELECT emp_id,salary,row_number() over(order by salary desc) AS rk FROM test_qaium ) where rk=2; But one of my friend ask me to find second MAX salary from…
Muhammad Abdul Qaium
  • 121
  • 1
  • 2
  • 10
1
vote
1 answer

Get latest exchange rate for all currencies

I have a database with a lot a currencies and each row has a currency, datestamp and the exchange rate. What I would like to have is a query that gets me the latest exchange rate for all the currencies based on the datestamp. I would not like the…
Oskar Kjellin
  • 21,280
  • 10
  • 54
  • 93
1
vote
1 answer

Get difference in top 2 salary of employee from each department

I have a simple employee table where i need to find difference between max of two salary in each department with maximum rank. Table columns i have: dept,sal,rank Sample data could be Dept Sal Rank Finance 10000 1 Finance 20000 …
Abhishek
  • 1,008
  • 1
  • 16
  • 39
1
vote
2 answers

Oracle SQL IN-Clause throws Error with Subselect

so here's my Problem: I got this SQL-Statement SELECT a.ID,a.CONTENTOF FROM MLMDATA.PV_STORAGE a WHERE 1=1 AND ID IN (113312,114583,114581,113472,114585,114580,113314) AND a.SEQ = (SELECT MAX(b.SEQ) FROM MLMDATA.PV_STORAGE b where a.ID = b.ID)…
Sunrunner
  • 15
  • 9
1
vote
1 answer

Complex SQL Query with MySQL?

I have a database with: [business] must have a [location] [location] may have many [business] [location] may have many [postcode] [postcode] may be for many [location] [postcode] must have a [state] I want a query to return the top 10 locations for…
oak
  • 119
  • 2
  • 7
1
vote
1 answer

select value from joined table based on lowest value in a different column

(SQL query on Oracle DB) I'm trying to display an equipment id number on a job record based on the highest priority (lowest number) of all tasks associated with the job. I also need to display all tasks for the job so I cannot simply limit the…
pablo
  • 13
  • 1
  • 3
1 2 3
…
99
100