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
-1
votes
2 answers

How to do grouping by multiple columns in MySQL Select Statement

Above is the output from my Select Statement in MySQL, can anyone please help me so that I can group above result by exp_date and username. So it will be only 1 row for any username for the same date. It should show lodging, boarding, laundry, and…
idleMind
  • 131
  • 5
  • 16
-1
votes
1 answer

MySql - Slow Query when adding Order by

I've an application which reads email messages (I download the emails from gmail inbox in my application's database) from MySql Database. and the Database has following structure Table1 (Contacts): ContactID (int) ContactName…
Meva159
  • 19
  • 3
-1
votes
1 answer

How do I include all max values within a row?

I'm very new to learning SQL, I apologize if my question isn't completely accurate. The question I'm trying to answer with this query is "What is the most popular music genre in each country?" I've had to use a subquery and it works, but I found…
Craig Myers
  • 1
-1
votes
2 answers

Getting latest quote data for stock

I am collecting quote data and chose opt_ticker and quoteTimeStamp as the primary key so that I can store unique quotes over time. I now want to create a view where I can see the latest quote for each opt_ticker (data base has other opt_tickers with…
nGX
  • 1,038
  • 1
  • 20
  • 40
-1
votes
2 answers

Database Query for Getting Max, Min of a column and corresponding values from other columns and Total Record Count from a Single Table in Hive

I have below dataset in a Hive table name - PUBLISH N.B. There can be duplicate records in PUBLISH DATE |HOUR|SOURCE|COL_TIMESTAMP |ID 20200101|14 |A |2020-01-01 14:18:53.016 GMT|ID_111 20200101|14 |A |2020-01-01…
user1326784
  • 627
  • 3
  • 11
  • 31
-1
votes
2 answers

How to optimize the response time of this SQL query of a large table?

I have a medium-large table (around 350000 entries and growing up). I need to fetch each last entry for the pair dev_id and var_id from the table. I actually I can fetch them, but the query takes around 20 seconds, and that it's unacceptable for my…
manespgav
  • 167
  • 1
  • 9
-1
votes
1 answer

Selecting the details of records with maximum orderID for a given fileID

For the customers with the same file ID, I am trying to select only those who have the maximum file ID. However, I keep getting error: subquery returned more than 1 value. I have tried filtering using where clause but it is not working. SELECT…
Bina
  • 3
  • 2
-1
votes
2 answers

Select Nth record from MySQL query from Millions of rows

I have a MySQL query as below; I would like to select the top record for each range of 600 records in a table with 1.8M records. So far I have to loop 3,000 times to accomplish this which is not an efficient solution. Database Schema; Table:…
jmsiox
  • 123
  • 10
-1
votes
3 answers

How to group by rows with most recent order?

Updated: Here is the demo and current result based on M Khalid Junaid's answer. The query still doesn't output my expected result. I have a very simple table and here is the values. id animal_id latitude longitude …
Devyn
  • 2,255
  • 7
  • 32
  • 40
-1
votes
3 answers

Datatable linq - Get the first row with maximum sum values

I have a datatable (with 2 columns): X Y AA 100 BB 150 AA 25 BB 10 CC 120 As you can see for : AA values the sum is 125 BB values the sum is 160 CC values the sum is 120 Them maximum sum is 160 for BB I want to obtains this…
Tratak
  • 138
  • 1
  • 9
-2
votes
1 answer

[SQL]How to return rows of a list of items that has their individual max date?

Currently I have a list of strings, let's say ('A,'B','C','D','E'), and say there is a table that has multiple dates associated with these strings. IE table looks something like this. String Dates A Jan 1 A Feb 1 A Mar…
Bryan C.H Li
  • 1
-2
votes
1 answer

MySQL query for lowest price for majority of prices in table

I'm trying to build a query where by I can extract the lowest price in the table, where the price is at least 10% or more of all the prices in the table. I understand I can use MIN(price) to get the bottom rate in the table. However, I want to use…
ACKA
  • 27
  • 1
  • 6
-2
votes
1 answer

Getting a min(date) AND max(date) AND their respective titles

I have three tables that I would like to select from Table 1 has a bunch of static information about a user like their idnumber, name, registration date Table 2 has the idnumber of the user, course number, and the date they registered for the…
Mat
  • 67
  • 1
  • 3
  • 17
-2
votes
1 answer

Filtering out most recent row in a join query

I have 2 tables with the following information. Unfortunately my basic knowledge of JOINS & MySQL isn't helping me achieve the result set I need... point 3 in particular is eluding me. Any help is greatly appreciated. ORDERS OrderID OrderDate …
Kristy
  • 1
-3
votes
2 answers

Need to write a query instead of 5 queries to get the result of all 5 states

The CSV file has around 62000 rows, It has states and county (county name is unique within that particular state). I had to write 5 queries on the view. Each query will retrieve for one of the states the State_Name, Date, MAX(SumConfirmed) the date…
user12017134
1 2 3
…
14
15