Highest and Lowest values

Question

I’ve got a table as follows …

StudentID ExamsPassed
Student3 2
Student4 3
Student5 2
Student6 1
Student7 3

I need to write a query that pulls out the highest and lowest values and returns something like

HighestAchievers ExamPassHighest LowestAchievers ExamPassLowest
Student 4 and Student7 3 Student6 1

I've done it using a sporoc for Highest and a sproc for Lowest using CTE's to loop through the data. Is there a way I can return the data
in a single sproc, without using a temp table?

Roy

Answer 1

Here are two solutions. The first is the "normal" solution for a relational database. That is, return the relevant rows and let the presentation layer take care of the rest. The second query gives you the output you ask for, although it is not very relational.

DROP TABLE IF EXISTS tbl
CREATE TABLE tbl(StudentID varchar(20), ExamsPassed int)
INSERT tbl(StudentID, ExamsPassed)
   VALUES('Student3', 2),
         ('Student4', 3),
         ('Student5', 2),
         ('Student6', 1),
         ('Student7', 3)

; WITH CTE AS (
   SELECT StudentID, ExamsPassed, 
          MAX(ExamsPassed) OVER() AS MaxPassed, 
          MIN(ExamsPassed) OVER() AS MinPassed
   FROM   tbl
)
SELECT StudentID, ExamsPassed
FROM   CTE
WHERE  ExamsPassed IN (MinPassed, MaxPassed)
ORDER BY ExamsPassed, StudentID
go
; WITH CTE AS (
   SELECT StudentID, ExamsPassed, 
          MAX(ExamsPassed) OVER() AS MaxPassed, 
          MIN(ExamsPassed) OVER() AS MinPassed
   FROM   tbl
), stringaggr AS (
   SELECT string_agg(StudentID, ' and ' ) WITHIN GROUP(ORDER BY StudentID) AS Studlist, ExamsPassed,
          row_number() OVER (ORDER BY ExamsPassed) AS rowno
   FROM   CTE
   WHERE  ExamsPassed IN (MinPassed, MaxPassed)
   GROUP  BY ExamsPassed
)
SELECT MIN(CASE WHEN rowno = 2 THEN Studlist END) AS HigestAchivers,
       MIN(CASE WHEN rowno = 2 THEN ExamsPassed END) AS ExamsPassHigest,
       MIN(CASE WHEN rowno = 1 THEN Studlist END) AS LowersAchivers,
       MIN(CASE WHEN rowno = 1 THEN ExamsPassed END) AS ExamsPassLowest
FROM   stringaggr

Answer 2

Brilliant
many thanks