Try
select top 1 * from Result order by Totalmark desc
[Edit]
To cater for more than one record having the same Totalmark, and then introducing other criteria just add extra bits to the
order by
clause e.g.
select top 1 * from Result order by Totalmark desc, Test1Mark1 desc
adding extra criteria as required...
select top 1 * from Result order by Totalmark desc, Test1Mark1, Test1Mark2, Test3Mark desc
[Edit 2 - OP requires all rows matching highest TestMark3 ordered by TotalMark]
Here's one way of doing it ...
declare @max3 int
set @max3 = (select max(Testmark3) from Result)
select * from Result where Testmark3 = @max3 order by Totalmark desc
{Edit 3 - further answers to OP comments - moved here from solution 2 as requested]
[Solution is not for original question but subsequent comment]
Apologies if I get too simplistic but I'm going to assume you know nothing rather than miss something out :-)
Well, the first bit is to work out how to limit the customers we are going to extract ... the hint gives you this. We need to calculate the "cost" and compare it to a fixed value...
WHERE (item_price * quantity_sold) > 200
Note that the column names are unique across the tables - however I still prefer to point out which table I'm looking at when listing columns so the final solution (below) will add some detail.
Next work out how to "join" the tables together... the id fields have nice enough names to work that out - the sales table will link to the customer table with customer_id and to the item table via item_id .. so we'll be getting stuff ...
FROM sales S
INNER JOIN customer C ON S.customer_id=C.customer_id
INNER JOIN item I ON S.item_id=I.item_id
Finally we'll put it all together with some appropriate things being retrieved from all of the tables ...
SELECT C.customer_name, S.bill_no, I.item_name, I.item_price, S.quantity_sold, (I.item_price * S.quantity_sold) AS cost
FROM sales S
INNER JOIN customer C ON S.customer_id=C.customer_id
INNER JOIN item I ON S.item_id=I.item_id
WHERE (I.item_price * S.quantity_sold) > 200
Note that I've used the table aliases through the SELECT, FROM and WHERE clauses - it's personal preference but saves a lot of unnecessary typing and helps keep things clear when these queries get larger and larger!
Note I've chosen to do "INNER JOIN" because I know that a sale must have had both a customer and an item involved (otherwise it wouldn't be a sale, right!)
This link gives an explanation of the other ways you could have joined these tables
http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html[
^]