How to find maximum value and associated field values ​​in SQL?

Question

Say I have a list of student names and their tracks. I want to know the highest grade and student, how can I write a single select statement for this?

Answer 1

Assuming you mean signs, not comments, use:

select name, mark from students where mark = ( select max(mark) from students ) 

This will result in a fairly efficient query. A subquery should be executed only once (unless your DBMS is disabled by the brain), and the result is passed to the second query. You can make sure that you have an index in the mark column.

Answer 2

 select name, remarks from student where remarks =(select max(remarks) from student) 

Answer 3

If you do not want to use a subquery:

 SELECT name, remark FROM students ORDER BY remark DESC LIMIT 1 

Answer 4

If you are using a database that supports a window,

 SELECT name, mark FROM (SELECT name, mark, rank() AS rk FROM student_marks OVER (ORDER BY mark DESC) ) AS subqry WHERE subqry.rk=1; 

This is probably not as fast as querying the style mark=(SELECT MAX(mark)... but worth checking out.

Answer 5

In SQL Server:

 SELECT TOP 1 WITH TIES * FROM Students ORDER BY Mark DESC 

This will return all students with the highest grade, whether there is only one of them or more than one. If you want only one line, leave the WITH TIES specifier. (But the actual string does not guarantee that it will always be the same.)

Answer 6

You can create a view and join it with the source table:

 V1 select id , Max(columName) from t1 group by id select * from t1 where t1.id = V1.id and t1.columName = V1.columName 

this is correct if you need max. values ​​with relevant information

Answer 7

Recently, I needed something “similar” to this post and wanted to share the technique. Suppose you have an Order and OrderDetail table, and you want to return information from the Order table along with the product name associated with the row with the highest price. Here you can pull it without subtexts, RANK, etc. The key is to create and aggregate, which combine the key and value from the detailed table, and then just max, and substitute the desired value.

 create table CustOrder(ID int) create table CustOrderDetail(OrderID int, Price money, ProdName varchar(20)) insert into CustOrder(ID) values(1) insert into CustOrderDetail(OrderID,Price,ProdName) values(1,10,'AAA') insert into CustOrderDetail(OrderID,Price,ProdName) values(1,50,'BBB') insert into CustOrderDetail(OrderID,Price,ProdName) values(1,10,'CCC') select o.ID, JoinAggregate=max(convert(varchar,od.price)+'*'+od.prodName), maxProd= SUBSTRING( max(convert(varchar,od.price)+'*'+od.prodName) ,CHARINDEX('*',max(convert(varchar,od.price)+'*'+convert(varchar,od.prodName)) )+1,9999) from CustOrder o inner join CustOrderDetail od on od.orderID = o.ID group by o.ID