How to find max value and its associated field values in SQL?

Say I have a list of student names and their marks. I want to find out the highest mark and the student, how can I w开发者_运维技巧rite one select statement to do that?

Assuming you mean marks rather than remarks, use:

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

This will generally result in a fairly efficient query. The subquery should be executed once only (unless your DBMS is brain-dead) and the result fed into the second query. You may want to ensure that you have an index on the mark column.

If you don't want to use a subquery:

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

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

If you are using a database that supports windowing,

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 probably does not run as fast as the mark=(SELECT MAX(mark)... style query, but it would be worth checking out.

In SQL Server:

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

This will return all the students that have the highest mark, whether there is just one of them or more than one. If you want only one row, drop the WITH TIES specifier. (But the actual row is not guaranteed to be always the same then.)

You can create view and join it with original 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 right if you need Max Values with related info

I recently had a need for something "kind of similar" to this post and wanted to share a technique. Say you have an Order and OrderDetail table, and you want to return info from the Order table along with the product name associated with the highest priced detail row. Here's a way to pull that off without subtables, RANK, etc.. The key is to create and aggregate that combined the key and value from the detailed table and then just max on that and substring out the value you want.

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