Menu
How do you return 1 value per row of the max of several columns:
TableName
[Number, Date1, Date2, Date3, Cost] I need to return something like this:
[Number, Most_Recent_Date, Cost] Query?
322
If you want to understand VALUE try this query which creates a virtual 1 column table: SELECT * FROM (VALUES (1), (5), (1)) as listOfValues(columnName) And this query which creates a virtual 2 column table: SELECT * FROM (VALUES (1,2), (5,3), (1,4)) as tableOfValues(columnName1, ColumnName2) Now you can understand why ...
The SQL MIN() and MAX() FunctionsThe MAX() function returns the largest value of the selected column.
You are grouping values there (see Group By in the end) and so your max function calculates Max value per group. If you wand an absolute max value, remove the grouping. See the top answer, it gives you a correct query, just replace LIMIT with SELECT TOP 1 as I mentioned there, this would be the syntax for sql server.
To select multiple columns from a table, simply separate the column names with commas! For example, this query selects two columns, name and birthdate , from the people table: SELECT name, birthdate FROM people; Sometimes, you may want to select all columns from a table.
Here is another nice solution for the Max functionality using T-SQL and SQL Server
SELECT [Other Fields], (SELECT Max(v) FROM (VALUES (date1), (date2), (date3),...) AS value(v)) as [MaxDate] FROM [YourTableName] Values is the Table Value Constructor.
"Specifies a set of row value expressions to be constructed into a table. The Transact-SQL table value constructor allows multiple rows of data to be specified in a single DML statement. The table value constructor can be specified either as the VALUES clause of an INSERT ... VALUES statement, or as a derived table in either the USING clause of the MERGE statement or the FROM clause."
131
This is an old answer and broken in many way.
See https://stackoverflow.com/a/6871572/194653 which has way more upvotes and works with sql server 2008+ and handles nulls, etc.
Original but problematic answer:
Well, you can use the CASE statement:
SELECT CASE WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1 WHEN Date2 >= Date1 AND Date2 >= Date3 THEN Date2 WHEN Date3 >= Date1 AND Date3 >= Date2 THEN Date3 ELSE Date1 END AS MostRecentDate If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
