Search Results
| Search type | Search syntax |
|---|---|
| Tags | [tag] |
| Exact | "words here" |
| Author |
user:1234 user:me (yours) |
| Score |
score:3 (3+) score:0 (none) |
| Answers |
answers:3 (3+) answers:0 (none) isaccepted:yes hasaccepted:no inquestion:1234 |
| Views | views:250 |
| Code | code:"if (foo != bar)" |
| Sections |
title:apples body:"apples oranges" |
| URL | url:"*.example.com" |
| Saves | in:saves |
| Status |
closed:yes duplicate:no migrated:no wiki:no |
| Types |
is:question is:answer |
| Exclude |
-[tag] -apples |
| For more details on advanced search visit our help page | |
Results tagged with greatest-n-per-group
Search options answers only
not deleted
user 39278
Use this for problems that involve returning one or more rows from each group in a data set. Despite the name, this tag is suitable for *minimum-* and *maximum-* per group queries.
1
vote
sql query using analytic functions
You can enumerate the rows per id ordered by data_date using ROW_NUMBER():
select name, id, attr1, attr2
, row_number() over (partition by id
order by data …
- 23.6k
7
votes
Select a distinct row
The row:
Sandy 2452356 24523
is preferred because it has the largest o_id. You can pick the largest o_id per salesperson via:
select max(o_id), salesperson
from T
group by salesperson
…
- 23.6k
2
votes
Accepted
Oracle SELECT FIRST?
If I understand you correctly you can enumerate the invoices per vendor and pick the last one (order by desc):
with invoices(invoice, vendor, cdate) as
(select 0, 0, date '2018-01-08' from dual …
- 23.6k
0
votes
SQL Query to select rows based on specific condition
Your sample data indicates that you want all activities after last PLAY per ID. One way is to ask for all activities where it does not exist a play at a later time:
SELECT t1.*
FROM t as t1
WHERE NOT …
- 23.6k
2
votes
Accepted
Get last two IDs from rows for last two days
You can use the rank function to get the last two terminalid's:
select terminalid, date, toolid, toolname, workcenterid
from (
select terminalid, date, toolid, toolname, workcenterid
, d …
- 23.6k
7
votes
Accepted
How to get the greatest element of a GROUP BY?
Probably easiest to do with a join against a derived table:
select a.*
from PurchaseOrders as a
join (
select sku, max(date) as date
from PurchaseOrders
group by sku
) as b
on a.sk …
- 23.6k
1
vote
Accepted
Selecting the max date out of a text group
You need to choose max(question_date) per subject. Something like:
WHERE question_date IN (
select max(question_date)
FROM messages
WHERE subject = a.subject
)
Another option is to en …
- 23.6k
3
votes
select n values per group
You can use the technique described in:
http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/
to mimic:
row_number() over (partition by ... order b …
- 23.6k
5
votes
multiple rows with the same maximum value
The problem is that you have more columns in the select than in the group by. This is allowed with default settings in MySQL, but may lead to in-deterministic results. Example:
engines, manufacturer, …
- 23.6k
1
vote
Using multiple GROUP BYs to get fewer records, not more
Your query is not valid SQL, it is only allowed if ONLY_FULL_GROUP_BY is missing from @@sql_mode. I moved your example to Fiddle1 and tried. Note that I set
set @@sql_mode=concat(@@sql_mode, ',ONLY_FU …
- 23.6k
0
votes
Accepted
Select first and last row based on each user id mysql
Here's one way:
SELECT id, timestamp, event
FROM (
SELECT id, timestamp, event
, ROW_NUMBER() OVER(PARTITION BY id ORDER BY timestamp asc) as r1
, ROW_NUMBER() OVER(PARTITION …
- 23.6k
2
votes
Accepted
Selecting minimum value in top grouping only
Here is one solution:
with t as (
select grp, emp_id, time_type, min(work_dt) as start_dt
, max(work_dt) as end_dt
from (
select row_number() over (partition by emp_id
…
- 23.6k
5
votes
Alternative to using MIN() in a subquery?
Your query uses a partial group. This is allowed in older versions of MySQL (with the default setting), and have probably caused a lot of pain and mysterious bugs over the years. To see why this happe …
- 23.6k
3
votes
MySQL Query Failed: Expression #6 of SELECT list is not in GROUP BY clause and contains non ...
You should add it to your config file, for example: /etc/my.cnf:
sql_mode='...'
Do note that this is not a bug, this is the way it is supposed to work. By disabling this check you risk getting inde …
- 23.6k
1
vote
Accepted
Previous Row based on double where condition and group max sum
Not the most elegant solution, but I believe we should divide it into two cases, first day or remaining days:
with classify (ts, value, init) as
-- classify rows as first_day or remaining day
(
sel …
- 23.6k