how can i rewrite a select query in this situation

Here are two table in parent/child relationship. What i need to do is to select students with there average mark:

CREATE TABLE dbo.Students(
 Id int NOT NULL,
 Name varchar(15) NOT NULL,
 CONSTRAINT PK_Students PRIMARY KEY CLUSTERED 
(

CREATE TABLE [dbo].[Results](
 Id int NOT NULL,
 Subject varchar(15) NOT NULL,
 Mark int NOT NULL
) 

ALTER TABLE [dbo].[Results]  WITH CHECK ADD  CONSTRAINT [FK_Results_Students] FOREIGN KEY([Id])
REFERENCES [dbo].[Students] ([Id])

I wrote a query like this :

SELECT   name , coalesce(avg(r.[mark]),0) as Avmark
FROM  students s
 LEFT JOIN results r ON 开发者_C百科s.[id]=r.[id]
GROUP BY s.[name]
ORDER BY ISNULL(AVG(r.[mark]),0) DESC;

But the result is that all of students with there avg mark in desc order.What i need is to restrict result set with students that have the highest average mark agaist other,i.e.if the are two students with avg mark 50 and 1 with 25 i need to display only those students with 50.If there are only one student with highest avg mark- only he must appear in result set.How can i do this in best way?

---

SQL Server 2005+, using CTEs:

WITH grade_average AS (
   SELECT r.id,
          AVG(r.mark) 'avg_mark'
     FROM RESULTS r
 GROUP BY r.id),
    highest_average AS (
   SELECT MAX(ga.avg_mark) 'highest_avg_mark'
     FROM grade_average ga)
SELECT DISTINCT
       s.name,
       ga.avg_mark
  FROM STUDENTS s
  JOIN grade_average ga ON ha.id = s.id
  JOIN highest_average ha ON ha.highest_avg_mark = ga.avg_mark

Non-CTE equivalent:

SELECT DISTINCT
       s.name,
       ga.avg_mark
  FROM STUDENTS s
  JOIN (SELECT r.id,
               AVG(r.mark) 'avg_mark'
          FROM RESULTS r
      GROUP BY r.id) ga ON ha.id = s.id
  JOIN SELECT MAX(ga.avg_mark) 'highest_avg_mark'
         FROM (SELECT r.id,
                      AVG(r.mark) 'avg_mark'
                 FROM RESULTS r
             GROUP BY r.id) ga) ha ON ha.highest_avg_mark = ga.avg_mark

---

If you're using a relatively new version of MS SQL server, you can use WITH to make this simple to write:

WITH T AS (
    SELECT
        name,
        coalesce(avg(r.[mark]),0) as mark
    FROM students s
    LEFT JOIN results r ON s.[id]=r.[id]
    GROUP BY s.[name])
SELECT name as 'ФИО', mark as 'Средний бал'
FROM T
WHERE T.mark = (SELECT MAX(mark) from T)

---

Is it as simple as this? For all versions of SQL Server 2000+

SELECT TOP 1 WITH TIES
   name, ISNULL(avg(r.[mark]),0) as AvMark
FROM
   students s
   LEFT JOIN
   results r ON s.[id]=r.[id]
GROUP BY
   s.[name]
ORDER BY
   ISNULL(avg(r.[mark]),0) DESC;

---

SELECT name as 'ФИО', 
coalesce(avg(r.[mark]),0) as 'Средний бал' 
FROM students s 
LEFT JOIN results r 
ON s.[id]=r.[id] 
GROUP BY s.[name] 
HAVING AVG(r.[mark]) >= 50
ORDER BY ISNULL(AVG(r.[mark]),0) DESC

about HAVING clause