Stack Overflow
Stack Overflow

Questions tagged [groupwise-maximum]

Questions regarding the common SQL problem selecting records holding the group-wise maximum of a certain column. Example: "For each article, find the dealer or dealers with the most expensive price."

212 questions
0
votes
3 answers

I want to get last row in mysql using group by

SELECT * FROM msg_messages m JOIN msg_status s on m.messageId = s.messageId JOIN msg_threads t ON t.threadId = m.threadId JOIN users u ON u.userId = s.userId WHERE t.threadId = 1 GROUP BY u.userId ORDER BY `m`.`date` DESC this is the query…
Awais Ayub
  • 389
  • 3
  • 13
0
votes
2 answers

Best way to return 450 rows with query that currently only returns one

I am developing a time and attendance application for the company I work for. Each employee will have a "work schedule" that is assigned to them. This schedule may remain the same for years and years, or it may change twice in one week. So I have…
Joe Gayetty
  • 1,521
  • 2
  • 22
  • 35
0
votes
0 answers

Groupwise Max With Filter Optimisation

I'm trying here to get rid of subquery in my groupwise max query that gives me unwanted using temporary Schema CREATE TABLE IF NOT EXISTS `rates` ( `id` int(11) NOT NULL AUTO_INCREMENT, `currency_id` int(11) NOT NULL, `rate` double NOT NULL, …
abr
  • 103
  • 1
  • 14
0
votes
1 answer

How to filter the max value and write to row?

Postgres 9.3.5, PostGIS 2.1.4. I have tow tables (polygons and points) in a database. I want to find out how many points are in each polygon. There will be 0 points per polygon or more than 200000. The little hick up is the following. My point table…
four-eyes
  • 10,740
  • 29
  • 111
  • 220
0
votes
2 answers

mysql groupwise max as second where condition

I have a working query that seems awfully inefficient; I'm wondering if I'm missing a simple way to improve it. Simple table: id date master_id ------------------------- 1 2015-02-01 0 2 2015-02-02 0 3 2015-02-03 0 4 2015-02-04 …
mike
  • 53
  • 6
0
votes
1 answer

Order by showing oldest result from join when grouped

Heres my query: select l.id, l.name, l.postcode, l.the_date, d.id as dealer_id, d.name as dealer_name, (select count(`id`) from `lead_copies` where `id_lead`=l.id) as total_copies, (select count(`id`) from `assigns` where `id_lead`=l.id) as…
inkd
  • 1,421
  • 1
  • 13
  • 16
0
votes
3 answers

get latest record for each ID

I would like to get the latest record for each server. Here is some example data: TimeGenerated SourceName ComputerName Message 2014-11-22 21:48:30 Windows Update Agent Server1 Update Failed 2014-11-22 21:42:30 Windows…
sspeed
  • 19
  • 3
0
votes
1 answer

Left join on last record (simple discussion board with topics, last posts and users)

I'm trying to make a simple discussion board and as a result I need topics titles with their authors and authors of topics last posts. So far I have this SELECT `t`.`id`, `t`.`title`, `t`.`date` as theme_date, `u`.`id` as user_id, …
Goldie
  • 1,570
  • 5
  • 21
  • 33
0
votes
1 answer

select row with max value of a group of rows in sql

I have a table "products" that has the columns "name" and price". There are multiple prices for the same name. The table looks like shampoo 7 shampoo 10 shampoo 8 bread 1 bread 1.5 water 0.5 water 0.7 ...... I want the row with the max…
Billy Grande
  • 577
  • 3
  • 11
  • 23
0
votes
4 answers

To get the multiple maximum-count of repeated values in mysql

How can I get the output for maximum count of repeated values from a table, which contains the repeated values corresponding to a column such a way that there are multiple different distinct values having maximum-counts. Consider r the table data…
Ritesh
  • 314
  • 7
  • 19
0
votes
3 answers

performing a join operation and filter records according to the MAX value of a partucular column

I need some assistance with the following scenario, I have two tables, MESSAGE_CONTEXT_TABLE (CONTEXT_ID,NAME,DESCRIPTION,PACKAGE) PK(CONTEXT_ID,PACKAGE) PACKAGE_INFO_TABLE (PACKAGE,DESCRIPTION,PACKAGE_ORDER) PK (PACKAGE_NAME) I need to perform a…
pragan
  • 143
  • 2
  • 11
0
votes
1 answer

Select max value from column along with its corresponding date

Anyone know how I can get the date that corresponds to the maximum score per game_id, per level please: ---------------------------------------- |id |game_id |level |score |date | ---------------------------------------- |1 |0 |1 …
user3241112
  • 69
  • 3
  • 8
0
votes
0 answers

SQL: Select all distinct and pick the row that has the max value in another column

| nid | vid | max_score | |-----+-----+-----------| | 1 | 1 | 1 | |-----+-----+-----------| | 1 | 2 | 1 | |-----+-----+-----------| | 2 | 1 | 1 | |-----+-----+-----------| | 2 | 2 | 2 …
Bobby S
  • 4,006
  • 9
  • 42
  • 61
0
votes
2 answers

Get last data for contracts

I want to select last information about client's balance from MySQL's database. I wrote next script: SELECT * FROM (SELECT contract_balance.cid, /*contract_balance.yy, …
Mixim
  • 972
  • 1
  • 11
  • 37
0
votes
1 answer

order by date of invoices which are multiple and in company table, but show only list of companies

I have a query that is working. However, it is only by ordered by company name, not by date of inside invoices of these companies: SELECT C.FULLNAME,C.COMPANY_ID,I.INVOICE_DATE FROM COMPANY C JOIN #dsn2_alias#.INVOICE I ON I.COMPANY_ID =…
Bato Dor
  • 841
  • 3
  • 11
  • 33
1 2 3
…
14 15