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 mytable ([ID] int, [TEMP] FLOAT, [DATE] DATE) ; INSERT INTO mytable ([ID], [TEMP], [DATE]) VALUES (8305, 16.38320208, '03/22/2002'), (8305, 17.78320208, '11/15/2010'), (8305, 16.06320208, '03/11/2002'), (8305, 18.06320208, '02/01/2007'), (2034, 5.2, '03/12/1985'), (2034, 2.24, '05/31/1991'), (2034, 6.91, '09/15/1981'), (2034, 7.98, '07/16/1980'), (2034, 10.03, '03/21/1979'), (2034, 6.85, '11/19/1982') ; ``` Querying for minimum/maximum of the TEMP and DATE columns: ``` SELECT ID, COUNT(TEMP) AS COUNT, MAX(TEMP) AS MAXTEMP, MAX(DATE) AS MAXDATE FROM mytable GROUP BY ID; ``` retrieves this: ``` | ID | COUNT | MAXTEMP | MAXDATE | |------|-------|-------------|------------| | 2034 | 6 | 10.03 | 1991-05-31 | | 8305 | 4 | 18.06320208 | 2010-11-15 | ``` But I would like to figure out how to retrieve this: ``` | ID | COUNT | MAXTEMP |MAXTEMPDATE | MAXDATE| | 2034 | 6 | 10.03 | 1979-03-21 |1991-05-31 | | 8305 | 4 | 18.06320208 | 2007-02-01 |2010-11-15 | ```

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 matches the given name and argument types. You might need to add explicit type casts. I know that I can cast to a character type or `ORDER BY` and `LIMIT 1`. I'm just curious as to why I have to use a workaround.

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 fast (0,04 sec): ``` SELECT MAX(Snapshot_Nummer) FROM snapshot_data; ``` Query 3 is fast (0,03 sec): ``` SELECT MAX(Snapshot_Nummer) FROM snapshot_booking; ``` Query 4 is fast (0,03 sec): ``` SELECT Snapshot_Nummer FROM v_booking ORDER BY Snapshot_Nummer DESC LIMIT 1; ``` In my case, all 4 queries are delivering the same result. My question Is there any way to execute the `MAX()` function fast and directly by using the MySQL view? `COUNT(*)` of the 3 tables: - `snapshot_data`: 5213 - `snapshot_booking`: 4113837 - `booking_data`: 1484 CREATE TABLE CREATE TABLE `snapshot_data` ( `Snapshot_Nummer` int(11) NOT NULL AUTO_INCREMENT, `Snapshot_Zeitpunkt` datetime DEFAULT NULL, PRIMARY KEY (`Snapshot_Nummer`) ) ENGINE=InnoDB AUTO_INCREMENT=5214 DEFAULT CHARSET=utf8 CREATE TABLE `snapshot_booking` ( `Magic_PK` int(11) NOT NULL AUTO_INCREMENT, `Snapshot_Nummer` int(11) NOT NULL, `Action_Link` int(11) NOT NULL, `bookingState` int(11) DEFAULT '0', PRIMARY KEY (`Magic_PK`), KEY `Snapshot_Nummer` (`Snapshot_Nummer`), KEY `Action_Link` (`Action_Link`), CONSTRAINT `snapshot_booking_ibfk_1` FOREIGN KEY (`Snapshot_Nummer`) REFERENCES `snapshot_data` (`Snapshot_Nummer`), CONSTRAINT `snapshot_booking_ibfk_2` FOREIGN KEY (`Action_Link`) REFERENCES `booking_data` (`Action_Link`) ) ENGINE=InnoDB AUTO_INCREMENT=4113838 DEFAULT CHARSET=utf8 CREATE TABLE `booking_data` ( `Action_Link` int(11) NOT NULL, `FaMaId` int(11) DEFAULT NULL, `BuchId` int(11) DEFAULT NULL, `MadaId` int(11) DEFAULT NULL, `StationId` int(11) DEFAULT NULL, `BOId` int(11) DEFAULT NULL, `BuchungCode` int(11) DEFAULT NULL, `DatumVon` datetime DEFAULT NULL, `DatumBis` datetime DEFAULT NULL, `BenutztVon` datetime DEFAULT NULL, `BenutztBis` datetime DEFAULT NUL

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 length of the data entered + 2 bytes. Please help me understand this. The max characters for varchar seems to be `8000`, which is way less than `2GB` worth of data. I see that there are records in this `varchar(max)` column of a specific table that have `len(mycolumn)` > 100 000. Thus I know I can get way more than `8000` characters into a `varchar(max)` column. Question 1: How does the `8000` characters come into play and where should I be aware of it? Question 2 : will a .net datareader query to this column always return the full result with 100 000+ character?

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 function MAX, but I'm not getting anywhere. I'm trying to get the max value for each of the past months .... Here's the data from the query, currently sorted by date (asc): ID Date Value 12410 01/03/2017 12:17 0.000178 12410 01/10/2017 11:36 0.000186 12410 01/17/2017 11:27 0.000189 12410 01/24/2017 13:09 0.000182 12410 01/31/2017 10:37 0.000169 12410 02/07/2017 11:03 0.000214 12410 02/14/2017 11:52 0.000176 12410 02/21/2017 10:51 0.000200 12410 02/28/2017 12:29 0.000194 12410 03/07/2017 08:39 0.000206 Here's the query: select AnalysisID as "ID" , AnalysisDateTime as "Date", AnalysisValue as "Value" from AnalysisValueTbl where AnalysisID = 12410 and DatePart(m, AnalysisDateTime) = DatePart(m, DateAdd(m, -3, getdate())) and DatePart(yyyy, AnalysisDateTime) = DatePart(yyyy, DateAdd(m,-3, getdate())) or AnalysisID = 12410 and DatePart(m, AnalysisDateTime) = DatePart(m, DateAdd(m, -2, getdate())) and DatePart(yyyy, AnalysisDateTime) = DatePart(yyyy, DateAdd(m,-2, getdate())) or AnalysisID = 12410 and DatePart(m, AnalysisDateTime) = DatePart(m, DateAdd(m, -1, getdate())) and DatePart(yyyy, AnalysisDateTime) = DatePart(yyyy, DateAdd(m,-1, getdate())) order by AnalysisValue desc

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"}, {"$unwind": "$rounds.matches"}, {"$project": { //"rounds.matches.team2.name":1, away_team: "$rounds.matches.team2.name", winner_away: { $cond: {if: {$gt:["$rounds.matches.score2","$rounds.matches.score1"] }, then:1,else :0} }, _id:0 } }, {"$group": { _id: {team_name:"$away_team"}, total_wins: {$sum:"$winner_away"} } } ) ``` and what I get is this: ``` { "_id" : { "team_name" : "Stoke City" }, "total_wins" : 6 } { "_id" : { "team_name" : "Newcastle United" }, "total_wins" : 2 } { "_id" : { "team_name" : "Chelsea" }, "total_wins" : 7 } { "_id" : { "team_name" : "Everton" }, "total_wins" : 5 } { "_id" : { "team_name" : "Bournemouth" }, "total_wins" : 6 } { "_id" : { "team_name" : "Manchester United" }, "total_wins" : 7 } { "_id" : { "team_name" : "Liverpool" }, "total_wins" : 8 } { "_id" : { "team_name" : "Manchester City" }, "total_wins" : 7 } { "_id" : { "team_name" : "Leicester City" }, "total_wins" : 11 } ``` If I add this to get just team's with more wins name, and number of wins ``` {"$group": { _id: "$team_name", max_wins: {$max: "$total_wins"} } } ``` I get this ``` { "_id" : null, "max_wins" : 11 } ``` Can someone tell me how to get the name as well? I can do it using sort, limit but this will not work in case of two or more teams having equal number of wins.

I have a table of Users: ``` |Username|UserType|Points| |John |A |250 | |Mary |A |150 | |Anna |B |600 | ``` and Levels ``` |UserType|MinPoints|Level | |A |100 |Bronze | |A |200 |Silver | |A |300 |Gold | |B |500 |Bronze | ``` And I'm looking for a query to get the level for each user. Something along the lines of: ``` SELECT * FROM Users U INNER JOIN ( SELECT TOP 1 Level, U.UserName FROM Levels L WHERE L.MinPoints < U.Points ORDER BY MinPoints DESC ) UL ON U.Username = UL.Username ``` Such that the results would be: ``` |Username|UserType|Points|Level | |John |A |250 |Silver | |Mary |A |150 |Bronze | |Anna |B |600 |Bronze | ``` Does anyone have any ideas or suggestions on how could I do this without resorting to cursors?

I'm using Python's `max` and `min` functions on lists for a minimax algorithm, and I need the index of the value returned by `max()` or `min()`. In other words, I need to know which move produced the max (at a first player's turn) or min (second player) value. `for i in range(9): new_board = current_board.new_board_with_move([i / 3, i % 3], player) if new_board: temp = min_max(new_board, depth + 1, not is_min_level) values.append(temp) if is_min_level: return min(values) else: return max(values) ` I need to be able to return the actual index of the min or max value, not just the value.

NumPy proposes a way to get the index of the maximum value of an array via `np.argmax`. I would like a similar thing, but returning the indexes of the `N` maximum values. For instance, if I have an array, `[1, 3, 2, 4, 5]`, then `nargmax(array, n=3)` would return the indices `[4, 3, 1]` which correspond to the elements `[5, 4, 3]`.

I have a dictionary where keys are strings, and values are integers. ``` stats = {'a': 1, 'b': 3000, 'c': 0} ``` How do I get the key with the maximum value? In this case, it is `'b'`. Is there a nicer approach than using an intermediate list with reversed key-value tuples? ``` inverse = [(value, key) for key, value in stats.items()] print(max(inverse)[1]) ```