All Questions
Tagged with subquery greatest-n-per-group
19 questions
1
vote
2
answers
93
views
How do I optimize this query that gets the newest entry of each group?
I have a query, that finds the newest item of each group based on the ID returned by a subquery. The problem is, that the table has a bit over 5000 entries and even with only this amount of entries ...
- 13
0
votes
1
answer
103
views
The most recent rows in for each type/agent
I have a table (still on MySQL 5.7) with lots of data. This table is written by some scripts. It works like a "checkin", where each agent is checking in in some period of time. It looks like ...
- 13
1
vote
0
answers
193
views
Return back only top N rows for each ID - SQL
I have written this SQL after browsing different topics. The topic I got the solution from -> Limit Records
MySQL version: 5.6
SELECT op.*
FROM (select op.*,
(@rn := if(@s = order_id, @...
- 11
0
votes
1
answer
2k
views
SELECTing n rows for each GROUP with WHERE condition
I SELECT n rows for each ArticleID from TagMap table
SELECT a.ArticleID,a.TagID FROM TagMap a
LEFT JOIN TagMap b ON a.ArticleID = b.ArticleID AND a.TagID < b.TagID
GROUP BY a.ArticleID,a.TagID
...
- 4,531
2
votes
2
answers
1k
views
Select maximum of a count in a grouped clause
I have the following tables:
Vehicles(v͟i͟n͟, model,category)
Sales(s͟a͟l͟e͟I͟D͟, staffID,customerID,date)
vehicleSold(saleID,v͟i͟n͟,salePrice)
When I join these tables using:
select YEAR(Sales....
- 45
4
votes
2
answers
2k
views
Select rows with the highest value for one ID, after grouping by two IDs
It is given in the problem that occurrences has to happen more than once, but the person (PID) should only be selected with the (agent) AID having the highest BustCount.
SELECT I.AgentID, I.PersonID, ...
0
votes
1
answer
17
views
Query finds no values when sharppend
I am trying to join two datasets and find the most recent certification date
I the following 2 datasets
Certifications
(1, 'Judge Level 1', 'Judge', 10, 2),
(2, 'Judge Level 2', 'Judge', 20, 2),
(3, '...
- 103
0
votes
0
answers
235
views
Improve MySQL query efficiency for first row in a group
I've written the query below in MySQL to get the top 10 top landing pages across all browser sessions.
Reading other similar posts about how to access the first row in a group, it seemed like the ...
- 101
3
votes
2
answers
29k
views
Limit left join for multiple rows
Since MySql doesn't support main alias reference in subquery is a bit challenging to create query with multiple rows where you have to limit rows of left join tables.
Let's overview simple example.
...
- 133
1
vote
0
answers
43
views
How to create subquery on multiple result columns?
I have two tables, both sharing the following columns:
table inventory: id, key, timestamp
table stocks: id, key, timestamp
I want to select all occurrences of the key from inventory table together ...
- 321
2
votes
1
answer
40
views
selecting next todo item from child table
I've a table with millions of records, relationship is one (object) to many (object_items).
object_items:
CREATE TABLE `object_items` (
`item_name` varchar(50) NOT NULL DEFAULT '',
`object_id` ...
- 123
1
vote
2
answers
156
views
Possible to rewrite this query as a join?
I'm fairly certain it's possible to rewrite this as a join query but I'm just not sure how to approach it:
SELECT
events.*,
(SELECT MAX(updated_at)
FROM events e
WHERE e.user_id =...
- 113
0
votes
1
answer
1k
views
Find first-time purchases with certain category
I'm trying to find the first purchase of a customer who buys a computer category table only within all time purchase to segment them using SQL. My trial is. The JOIN part worked by someone else, so I ...
- 113
0
votes
1
answer
3k
views
Use ANY/ALL to retrieve customer who placed the highest number of orders
I have this tables, and i would like to retrieve the customer who placed the highest number of orders.
i wrote this query:
select * from customers where cust_id=(
select cust_id from orders
group by ...
- 341
2
votes
1
answer
5k
views
How to limit an inner query by outer query attribute
I have a requirement to sort a table by multiple criteria, select the first and associate with another table. The DB is Postgres.
Let's say we have players and teams where player has played from ...
- 351
1
vote
2
answers
124
views
Is there a way to do the following query with joins instead of a sub query?
I am using SQL Server 2014 and I want to eliminate having to use the following sub query so I can create an indexed view.
The only thing similar to my problem is the greatest-n-per-group queries but ...
- 13
0
votes
1
answer
843
views
Select records as groups based on sequence and date
I have data stored as:
CompID | Seq | Udate | Utime
--------+-----+-----------+-------
mx433 | 1 | 1/1/2012 | 13:54
mx433 | 1 | 1/2/2012 | 13:43
mx433 | 2 | 1/4/2012 | 14:23
mx433 ...
- 1
3
votes
2
answers
2k
views
Find the second highest value from a subquery or derived table
I have a subquery which returns the following results.
'c003', '120000.00'
'c002', '90000.00'
'c001', '20000.00'
'c005', '8000.00'
'c004', '5000.00'
I want to find the second highest value from this....
- 153
1
vote
2
answers
621
views
optimization of expensive subquery
The following query gets the last report (where latitude and longitude and secs are <> 0) associated with the specified units:
SELECT
reports.*
FROM
reports
INNER JOIN
...
- 1,879