SQL - How to find the highest number in a column?
You can do
SELECT MAX(ID) FROM Customers;
Find highest value in a column in SQL Server
A correlated subquery usually has good performance:
select t.*
from t
where t.version = (select MAX(t2.version) from t t2 where t2.projectname = t.projectname);
In particular, you want an index on (projectname, version)
for optimal performance.
How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
You are so close! All you need to do is select BOTH the home and its max date time, then join back to the topten
table on BOTH fields:
SELECT tt.*
FROM topten tt
INNER JOIN
(SELECT home, MAX(datetime) AS MaxDateTime
FROM topten
GROUP BY home) groupedtt
ON tt.home = groupedtt.home
AND tt.datetime = groupedtt.MaxDateTime
Query to find nᵗʰ max value of a column
You could sort the column into descending format and then just obtain the value from the nth row.
EDIT::
Updated as per comment request. WARNING completely untested!
SELECT DOB FROM (SELECT DOB FROM USERS ORDER BY DOB DESC) WHERE ROWID = 6
Something like the above should work for Oracle ... you might have to get the syntax right first!
What is the simplest SQL Query to find the second largest value?
SELECT MAX( col )
FROM table
WHERE col < ( SELECT MAX( col )
FROM table )
SQL select only rows with max value on a column
At first glance...
All you need is a GROUP BY
clause with the MAX
aggregate function:
SELECT id, MAX(rev)
FROM YourTable
GROUP BY id
It's never that simple, is it?
I just noticed you need the content
column as well.
This is a very common question in SQL: find the whole data for the row with some max value in a column per some group identifier. I heard that a lot during my career. Actually, it was one the questions I answered in my current job's technical interview.
It is, actually, so common that Stack Overflow community has created a single tag just to deal with questions like that: greatest-n-per-group.
Basically, you have two approaches to solve that problem:
Joining with simple group-identifier, max-value-in-group
Sub-query
In this approach, you first find the group-identifier, max-value-in-group
(already solved above) in a sub-query. Then you join your table to the sub-query with equality on both group-identifier
and max-value-in-group
:
SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
SELECT id, MAX(rev) rev
FROM YourTable
GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev
Left Joining with self, tweaking join conditions and filters
In this approach, you left join the table with itself. Equality goes in the group-identifier
. Then, 2 smart moves:
- The second join condition is having left side value less than right value
- When you do step 1, the row(s) that actually have the max value will have
NULL
in the right side (it's aLEFT JOIN
, remember?). Then, we filter the joined result, showing only the rows where the right side isNULL
.
So you end up with:
SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;
Conclusion
Both approaches bring the exact same result.
If you have two rows with max-value-in-group
for group-identifier
, both rows will be in the result in both approaches.
Both approaches are SQL ANSI compatible, thus, will work with your favorite RDBMS, regardless of its "flavor".
Both approaches are also performance friendly, however your mileage may vary (RDBMS, DB Structure, Indexes, etc.). So when you pick one approach over the other, benchmark. And make sure you pick the one which make most of sense to you.
SQL : get Min and Max value in one column
I Finally figured out a simple code for what i wanted.
select emp_name, salary
from employees
where salary = (select max(salary) from employees)
union all
select emp_name, salary
from employees
where salary = (select min(salary) from employees);
I didn't know about Union.
Thank you all for your contribution
How to Identify the column with the max value per row
You can do this with case
logic. Assuming none of the values are NULL
:
select t.*,
(case greatest(x, y)
when x then 'x'
when y then 'y'
end)
from t;
select max value from a string column in SQL
Since you are maxing a string it is sorting alphabetically where C is larger than AAA and 9 is larger than 10. Remove the letters and cast it as an int then get the max. Given that it will always be Ord-### we can remove the Ord- and cast the remainder as an INT.
SELECT
MAX(CAST(SUBSTRING(OrderNo,5,LEN(OrderNo)-4) AS INT))
FROM OrderSummary
WHERE OrderNo LIKE 'Ord-%'
Related Topics
How to Order by Column with Non-Null Values First in Sql
Pass SQL Functions in Dplyr Filter Function on Database
Counter_Cache Has_Many_Through SQL Optimisation, Reduce Number of SQL Queries
How to Drop All Foreign-Key Constraints on a Table in SQL Server 2000
Oracle SQL Order by in Subquery Problems!
Count Number of Occurrences for Each Unique Value
How to Return Rows from a Declare/Begin/End Block in Oracle
Selecting Multiple Rows by Id, Is There a Faster Way Than Where In
How to Select a Fixed Number of Rows for Each Group
Creating a Table from a Query Using a Different Tablespace (Oracle Sql)
How to Generate SQL from Dbplyr Without a Database Connection
Sql Query to Find Last Day of Current Month
In SQL Server, How to Create While Loop in Select
How to Find Out If an Oracle Database Is Set to Autocommit
How to Pass Two SQL Tables as Input Parameter for R Codes in SQL Server