Questions tagged [max]

Finding the maximum value in a group of items

Filter by
Sorted by
Tagged with
2 votes
2 answers
614 views

Ignore duplicate rows in a join - and take only one of them

In MySQL I have tables, simplified as follows: companies (id, name, rank, trade_sector_id) orders (id, company_id, paid, subtotal) trade_sectors (id, label) companies_notes (id, company_id, notes) I ...
Ade's user avatar
  • 123
0 votes
0 answers
142 views

Find maximum of 'jsonb_path_query_array' output

I'm using postgres with a jsonb field that looks something like this: {[[1.2, 'blah'], [3.4, 'blahhh']} I'm extracting the numbers by using: jsonb_path_query_array(json_row, '$[*][0]') Which gives ...
Nathaniel Bubis's user avatar
0 votes
2 answers
84 views

Selecting last value to be entered each month

I am looking to pull a closing balance from the database for each month. I have tried SELECT CloseBal As 'Balance', MONTHNAME(DateTime) as 'Month', DateTime FROM Table1 WHERE MAX(DateTime) Group By ...
Paulmcf1987's user avatar
0 votes
0 answers
80 views

Overlap on Max/Min dates

I have data in a table that has gotten cut up with multiple start and end dates. This is due to an error that has happened a few times in the last year. Apparently causing the system to not see the ...
daveyjones88's user avatar
1 vote
1 answer
135 views

Query for MAX/MIN of non-clustered index field for partitioned table

We have a table partitioned on a two-year based hash function from the system date at the insertion time (let's call it PartitionKey). It also has a non-clustered index on a single field (Key). We ...
PM 77-1's user avatar
  • 123
0 votes
2 answers
520 views

What if we do not use MAX function

I was supposed to use a script to get the MAX value from a table. It should have looked like this BEGIN DECLARE @MaxID AS INT SELECT @MaxID = MAX([Id]) FROM dbo.suggestion Insert into suggestion ...
ispostback's user avatar
1 vote
1 answer
209 views

How to filter results by specifying the criteria you want the MAX date stamp for?

Please bear with me as I am a beginner and could not find this answer elsewhere. I am working with employee data and need to determine which Product Group a given Team was associated with on the last ...
Ryan's user avatar
  • 13
-1 votes
2 answers
113 views

How to write query to select highest value from same date

please help me write this query for MySQL. I want to select highest value from same datetime. So if there are multiple equal datetime value, return only one row with highest price. For summary, one ...
Sahasrar's user avatar
4 votes
2 answers
32k views

How to retrieve the maximum value and its corresponding date in a table

I'm trying to figure how to retrieve minimum/maximum values and minimum/maximum dates from a data set, but also the date value that corresponds to each minimum/maximum value. Example Data CREATE TABLE ...
Mr. Geologist's user avatar
1 vote
2 answers
4k views

How to get the second highest value per group?

Getting the second highest value from a table has been solved many times, but I'm looking for the second highest value in each group. Given this table: +----+-----+ | A | 10 | | A | 20 | | A | ...
RonJohn's user avatar
  • 615
0 votes
3 answers
192 views

Statement of total precipitation for each hour, day or month

I store the data from the rain gauge in mySQL and needed help with listing the data. I save the data every 5 minutes and use the save date in the form of a timestamp. At the end of each hour, ...
Novosad's user avatar
0 votes
3 answers
153 views

Find Max() updated time for a group of users

Database is MariaDB 10.3.25 I have 2 columns that are relevant: UserID and LastUpdate UserID consists of user@domain LastUpdate is a date field. Here is my issue - I have the current query: select a....
TheDemonLord's user avatar
2 votes
2 answers
3k views

Choosing values from multiple columns based on latest value of one column

I have an Azure Cosmos DB that contains columns DateTime, userName, temperature, and bloodPressure, plus many other columns. There are several distinct userName values, and each userName has ...
KAE's user avatar
  • 143
0 votes
1 answer
293 views

why is there an index range scan on max() query?

I have a table that logs the transmission of data to another system. create table transfer_log (id number(10), fk_data number(10), first_sent_time ...
EasterBunnyBugSmasher's user avatar
1 vote
1 answer
413 views

SQL Only display data with Max latest date date

tracking_id date_action action_id 1 2021/01/24 4 1 2021/01/26 3 2 2021/01/25 67 2 2021/01/24 8 4 2021/01/27 10 5 2021/01/24 6 4 2021/01/25 55 Result should be: tracking_id date_action ...
Pushpa 01's user avatar
0 votes
2 answers
54 views

Max of a query which is inside

Create a database for a drone delivery startup that distributes pizzas via drones. The startup has partnerships with several drone manufacturers and pizza shops. The entities of interest to the ...
hackermanwow's user avatar
0 votes
1 answer
598 views

Query returns all values instead of the row of the one with the max value

I have this database https://pastebin.com/WWRxApWn I need the row of the product with the greatest unitprice. I did: SELECT id, MAX(unitprice) FROM product GROUP BY id; Instead, it returned every row ...
Segmentation fault's user avatar
0 votes
1 answer
469 views

How to select maximum row value using optimal way

I have a large table: +----+-----------+------------+--------+--------+--------+ | Id | lClientId | datAccess | lProp1 | lProp2 | lProp3 | +----+-----------+------------+--------+--------+--------+ | ...
John's user avatar
  • 11
7 votes
3 answers
10k views

Why is there no max(uuid)/min(uuid) function?

Why can I use a UUID to sort rows: SELECT uuid_nil() ORDER BY 1; But I cannot compute the maximum value: SELECT max(uuid_nil()); [42883] ERROR: function max(uuid) does not exist Hint: No function ...
xehpuk's user avatar
  • 297
0 votes
1 answer
29 views

Need to Bring Back most Recent Departure Date from Sales table for Customers in Recipient table

Looking at 3 Tables - Recipient, HouseholdScores, Sales. I need to bring back the most Recent tsDepart (departure date) from sales for the iCustomerId in the Recipient Table. There is no Depart date ...
Sam Tresidder's user avatar
2 votes
1 answer
1k views

How to get MAX numeric value from a column with char values in MySql

I would like to know how can I get the max value only within the numeric values in a column with char values in a MySql database: Supposing the column is as follows: col --------- ALL USD ...
rainman's user avatar
  • 195
0 votes
0 answers
40 views

MAX with group by by 2 columns

I have a query create table film_actor ( actor_id integer, film_id integer); create table film ( film_id integer, title varchar); create table actor ( actor_id integer, ...
Kirill Berlin's user avatar
0 votes
0 answers
66 views

I'm having trouble generating a list of downloadable items (data from three tables) with a column showing most recent download time for each

I am using the following query to generate a comprehensive download log with data from multiple tables (reduced to three for this example): SELECT wp_download_log.ID ,wp_download_log....
user2461674's user avatar
0 votes
0 answers
56 views

query start, due, complete dates for first and last time phases from phase tracking table

linked excel file I have a phase tracking table which consists of the fields record id, phase name, record order number etc: RECORD_ID,phase,RECORD_ORDER,SUBMITTED_DATE,DUE_DATE,COMPLETED_DATE 1657,...
arthur's user avatar
  • 11
0 votes
1 answer
34 views

Postresql, max returns all rows with same ids

System PostgreSQL 11.5 (Ubuntu 11.5-1.pgdg19.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 8.3.0-6ubuntu1) 8.3.0, 64-bit Issue Need return one max value from table for every product_id (1 ...
genderbee's user avatar
  • 191
1 vote
1 answer
1k views

DB2 SUM of MAX values, single table

I have looked through many of the questions asked and answered but can't seem to find my answer, even kludging the provided answers into my queries - most usually fail, the remainder provide garbage ...
Andrew Holko's user avatar
-1 votes
1 answer
778 views

How can I get the max value for each column?

If data of table like this, [co1:varchar][col2:varchar][col3:varchar][col4:timestamp] And, Follow is data example. 224 / 755 / 542 / 2019-05-23 10:12:23 230 / 741 / 530 / 2019-05-23 10:12:23 221 / ...
user3920450's user avatar
0 votes
1 answer
16k views

Select ONLY Max(date) when there's repeating dates

Here is a sample of the data contained in my Tracking table: tracking_id date_action action_id staffing_id ------------- ----------- ----------- ----------- 1 2019/03/04 4 ...
DrD4rk's user avatar
  • 33
4 votes
5 answers
13k views

Select unique records from multiple records based on max date

I have table like below +----+----------+-----+-------------+--------+ | ID | ADDR | IND | update_Date | Sq_NBR | +----+----------+-----+-------------+--------+ | 1 | PHYSICAL | Y | 11-Oct ...
heye's user avatar
  • 129
1 vote
1 answer
107 views

What is the function that determines the maximum value in a series?

Say I have a table with columns C and D (among others but these are the relevant ones) with the date type. I am instructed to update the values in C to be 90 days before its corresponding value in D, ...
Hello's user avatar
  • 113
0 votes
2 answers
94 views

I only want one maximum value but it returns all the rows [closed]

I need help with this query. I'm trying to get a value for a customer that spends the most amount every month in 2018 but it doesn't work: PROMPT 'Customer that has Spent the Most on Specific Month' ...
jOJO's user avatar
  • 11
0 votes
3 answers
40 views

Get Primary records where N most recent Secondary records match condition

I want a list of Authors (primary records) whose 2 most recent Books (secondary records) are best sellers. INSERT INTO Authors (`author_id`, `author_name`) VALUES (41, 'Heida Palffrey'), (...
Thunder Rabbit's user avatar
2 votes
2 answers
2k views

Get related row based on two MAX Values

I bumped into a use case where I wanted to write a SQL query to retrieve a row based on 2 MAX values of the table. I tried a couple of combinations but no luck as of yet. Here's a sample table and the ...
L Mudiyanse's user avatar
0 votes
1 answer
2k views

MySQL - maximum of sum over different months [duplicate]

I have a table called commodities where I have nature_of_goodscolumn in it. nature_of_goods contains all the different types of goods like machinery, household etc. I have another column called as ...
Sai kiran's user avatar
1 vote
1 answer
60 views

Max values on a group of fields

Does anyone know how can I select rows that contain maximum value for some group of fields based on some criteria? For instance, let's say I have the following table: Table1: ID fieldA fieldB ...
Fábio's user avatar
  • 13
0 votes
1 answer
40 views

How can I find state name which has the most vehicle accidents in the database?

I have a table "ACC_DATA" that has the following columns: ACCIDENT_ID, STATE_CODE, ACC/DATE and a second table "US_STATES" which has the following columns: STATE_CODE and STATE_NAME Each accident ...
cesium133's user avatar
  • 117
4 votes
2 answers
2k views

select MAX() from MySQL view (2x INNER JOIN) is slow

I want to optimize my MySQL view v_booking. At the moment I am wondering about the following issue: My queries Query 1 is slow (47,48 sec): SELECT MAX(Snapshot_Nummer) FROM v_booking; Query 2 is ...
matt's user avatar
  • 143
0 votes
1 answer
47 views

Form groups computing max - min depending on number of peers

This is table: CREATE table point ( datetime timestamp, value float) I have data in some range of time 7:00 - 7:15: 0 5 : 5 7:15 - 7:30: - - : null 7:30 - 7:45: 7 - :...
Viet Dao's user avatar
15 votes
2 answers
32k views

Understanding varchar(max) 8000 column and why I can store more than 8000 characters in it

From this Microsoft doc,+ n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual ...
Peter PitLock's user avatar
2 votes
1 answer
1k views

MySQL Grouping Data Together From Same User

I need a database guru. I'm trying to solve a problem that I'm pretty sure can be solved with a simple query I cant figure out. First, this is the table ve_messages: MessageID | RecieverID | SenderID ...
Mars's user avatar
  • 65
1 vote
2 answers
1k views

MySQL query: select max(A) and use MAX(pkey) if multiple max(A) records exist

I've been trying to find a solution for this problem using just 1 join. Is it possible? table: pkey | uid | A | B | C 100 | 1 | 5 | 2 | 101 | 1 | 5 | 3 | 'should find this' 102 | 1 | 4 | 6 ...
user2252031's user avatar
5 votes
1 answer
5k views

What would be the maximum number of records that can be indexed with a three levels B-tree ? B+ tree?

I am learning dynamic tree-structure organizations and how to design databases. Consider a DBMS with the following characteristics : file pages with size 2048 bytes pointers of 12 bytes page header ...
Revolucion for Monica's user avatar
0 votes
2 answers
155 views

MySQL: How to get the maximum disposition and the maximum date from my database

I want to get the top disposition of every ID where 1 is the lowest and 13 is the highest, however when there are IDs with similar disposition (e.g. ID #2 having multiple dispositions of '9') I cannot ...
B. Ellana's user avatar
0 votes
1 answer
17k views

Finding row with Max value with inner join and sub-query

I need to SUM the total values of each student using INNER JOIN and then find the row with maximum value. I have used the below query to find the SUM of each student and sort in descending order ...
tryllz's user avatar
  • 13
4 votes
2 answers
21k views

Find Max Value for each month for the last 3 months, properly

I have a query going that gets data for an ID for the last 3 months. I need to tweak it so I get the highest value for each of the three months. I've tried a couple of things with the aggregate ...
Emperor1951's user avatar
-1 votes
2 answers
174 views

Refining Productivity & Efficiency Query

I'm trying to write a query that is, in effect, a Productivity and Efficiency Report for measuring the statistics of those of us (such as myself) who type up the paper work orders that our mechanics ...
Ben Williamson's user avatar
5 votes
2 answers
28k views

Return document that has $max value in specific field using mongodb

I have a json file that has some football results. I need to calculate which team won the most away games, and how many wins there were. My query is this: db.football.aggregate( {"$unwind": "$rounds"...
Fierce82's user avatar
  • 183
-2 votes
2 answers
20k views

Why is Max Date not working?

I have a simple query that I just want to return the most recent date for. I can't figure out why I can't do this:
Lee's user avatar
  • 1
1 vote
1 answer
715 views

Why is the value - despite of using GREATEST() - still out of range?

I tried the following to decrease a counter after a post was deleted by using GREATEST() so it does not result a negative value: SELECT GREATEST(0, posts - 1) FROM users WHERE id = 123 but it ...
mgutt's user avatar
  • 133
0 votes
2 answers
9k views

Finding the name of the supplier that supplies the most parts

I have the following schema: Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) Now what I'm ...
user3502486's user avatar