Skip to main content
32 votes
Accepted

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

I can see why you're misunderstanding this - it's a little tricky. These are all valid: VARCHAR(1) - one character string VARCHAR(4000) - 4,000 characters VARCHAR(8000) - 8,000 characters - and if ...
Brent Ozar's user avatar
18 votes
Accepted

Query to select max value on join

Your existing query is close to something that you could use but you can get the result easily by making a few changes. By altering your query to use the APPLY operator and implementing CROSS APPLY. ...
Taryn's user avatar
  • 9,676
9 votes
Accepted

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

For SQL Server, you could do something like this. DECLARE @t TABLE (Id INT, DateVal DATETIME, ValueVal DECIMAL(18, 9)); INSERT @t ( Id, DateVal, ValueVal ) SELECT Id, DateVal, ValueVal FROM ( ...
Erik Darling's user avatar
  • 40.8k
9 votes

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

You can achieve it using a common table expression (CTE) and ROW_NUMBER(): ;WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [TEMP] DESC) AS rn FROM mytable ) SELECT ...
Ronaldo's user avatar
  • 5,089
8 votes

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

I guess it's an oversight. To me it doesn't make any sense that you can do >, greatest, least, order by etc. but not do min/max on it. That's just absolutely counter intuitive and not what one ...
scravy's user avatar
  • 180
7 votes

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

Question 1: How does the 8000 characters come into play and where should I be aware of it? Setting n to 8000 causes 8000 characters to come into play. You need to be aware of the Precision, Scale, ...
Bill's user avatar
  • 124
6 votes

max(max(col1), max(col2), ...) does not work

That should be GREATEST(), not MAX(). MAX() is for aggregating over values from rows, GREATEST() is for aggregating over a list of values (from columns, expressions). You can either find the 3 max ...
ypercubeᵀᴹ's user avatar
6 votes
Accepted

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

A potentially more efficient but more obscure way is to just extract the desired value using your original group by SELECT ID, COUNT = COUNT(TEMP), MAXTEMP = MAX(TEMP), ...
Martin Smith's user avatar
  • 84.7k
5 votes

What if we do not use MAX function

Sometimes you get lucky. Your old query would be assigning the variable @MaxId to the last result it got from your query (because it was constantly reassigning the variable per result) SELECT [Id] ...
Andrew Sayer's user avatar
  • 2,668
4 votes
Accepted

How to UPDATE NULL values by MAX()+1?

Assuming that you want - if the max(number) in the whole table is say, 27 - to update all the rows with NULL, to 28. You need a subquery or a derived table to first find this max and then join back ...
ypercubeᵀᴹ's user avatar
4 votes
Accepted

Max values on a group of fields

If I understood this correctly, you can use ROW_NUMBER: WITH CTE AS ( SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY fieldA, fieldB ORDER BY fieldC DESC) FROM dbo.YourTable ) ...
Lamak's user avatar
  • 2,576
4 votes
Accepted

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

The function is called GREATEST() GREATEST returns the greatest of a list of one or more expressions. Oracle Database uses the first expr to determine the return type. If the first expr is numeric, ...
ypercubeᵀᴹ's user avatar
4 votes

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

I'd say that nobody saw any use in calculating the maximum of UUIDs, and I cannot see one either. That said, it is easy to use CREATE AGGREGATE to define your own max, if you really need that.
Laurenz Albe's user avatar
  • 51.9k
4 votes

How to get the second highest value per group?

Assuming distinct values per group. So we need not break ties. Assuming at least 2 rows per group - or the following query breaks. (You'd need to do more, starting by defining the "2nd highest ...
Erwin Brandstetter's user avatar
4 votes
Accepted

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

Supposing following setup CREATE PARTITION FUNCTION [pf](INT) AS RANGE RIGHT FOR VALUES (1,2,3,4,5) CREATE PARTITION SCHEME [ps] AS PARTITION [pf] ALL TO ([PRIMARY]) CREATE TABLE T1( [PartitionKey] ...
Martin Smith's user avatar
  • 84.7k
4 votes
Accepted

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

Making companies_notes.company_id unique per your comment, will certainly solve the problem (once you've removed or merged the extra entries to allow the constraint to be applied). But in the meantime,...
Steve's user avatar
  • 436
3 votes

Why is Max Date not working?

It looks to me like you don't quite understand how to use GROUP BY. A query with GROUP BY will identify the unique sets of values for the grouped-by columns, and then will allow you to use aggregate ...
RDFozz's user avatar
  • 11.6k
3 votes

MIN / MAX values with Date

Your query is invalid SQL, but MySQL accepts it with the default settings. It will give you a random result. You can change this behavior by adding 'ONLY_FULL_GROUP_BY' to sql_mode. EDIT: Similar to @...
Lennart - Slava Ukraini's user avatar
3 votes

Query to select max value on join

The following solution uses a common table expression that scans the Levels table once. In this scan, the "next" points level is found using the LEAD() window function, so you have MinPoints (from the ...
Daniel Hutmacher's user avatar
3 votes
Accepted

DB2 SUM of MAX values, single table

I think you want multiple aggregations: First find the max for each ID-Source combination, then sum for every ID: select ID, sum(MaxNumImages) as SumMaxNumImages from ( select ID, max(...
ypercubeᵀᴹ's user avatar
3 votes

What if we do not use MAX function

A SELECT statement does not have a guaranteed order by unless you ask for it. In the case it works, it fills the variable 'accidentally' with the last value, as that seems for SQL the most efficient ...
Peter's user avatar
  • 2,267
3 votes

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

I thought that by using MAX aggregating function on companies_notes.notes only one would be considered...but I still don't understand why the aggregating function MAX doesn't resolve it Aggregate ...
J.D.'s user avatar
  • 37.5k
2 votes

Query to select max value on join

I think you can use an INNER JOIN -as a performance issue you can also use LEFT JOIN instead- with ROW_NUMBER() function like this: SELECT Username, UserType, Points, Level FROM ( SELECT u.*,...
shA.t's user avatar
  • 161
2 votes

Query to select max value on join

Why not do it using only the rudimentary operations, INNER JOIN, GROUP BY, and MAX: SELECT U1.*, L1.Level FROM Users AS U1 INNER JOIN ( SELECT U2....
SlowMagic's user avatar
  • 151
2 votes

How to get one MAX() between SUMs of several columns in SQL?

If you are using SQL Server, this is a good scenario to use UNPIVOT. You can use write a SELECT to give you the single-row result of SUM values, then apply UNPIVOT to essentially convert that row ...
AMtwo's user avatar
  • 16.2k
2 votes

How to get one MAX() between SUMs of several columns in SQL?

It's never going to be pretty, but heres one attempt: select gt, case gt when math then 'Math' when physics then 'Physics' when english then 'English' ...
Lennart - Slava Ukraini's user avatar
2 votes
Accepted

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

If your posts column is BIGINT UNSIGNED then unsigned values have to be 0 or more, so -1 is out of range. MariaDB [test]> desc tbl1; +-------+---------------------+------+-----+---------+-------+ |...
atokpas's user avatar
  • 8,634
2 votes
Accepted

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

One way to accomplish what you're looking for is by using the ROWNUM pseudocolumn. Note that you need to define your ORDER BY in an inline view or by some other method to avoid a common trap that ...
Joe Obbish's user avatar
  • 32.2k

Only top scored, non community-wiki answers of a minimum length are eligible