Let have a simple table to working-on and some dummy data.
SQL:
CREATE TABLE Course(
CourseTitle nvarchar(32) NOT NULL,
CourseCapacity int NOT NULL);
If we want to get second highest value in that table, the first idea may we have is use sub-query (there are many answers that we can find on internet). Some of those solutions:
(1) SQL:
SELECT * FROM Course c1 WHERE (2 - 1) =
(SELECT COUNT(DISTINCT(CourseCapacity))
FROM Course c2
WHERE c2.CourseCapacity > c1.CourseCapacity);
(2) SQL:
SELECT TOP 1 * FROM Course WHERE
CourseCapacity < (SELECT MAX(CourseCapacity) FROM Course)
ORDER BY CourseCapacity DESC;
But, how if only used single query?
The first idea that I got is use JOIN. And the possible JOIN type is NATURAL JOIN for this case. But that still not answer the problem. So, I tried to JOIN the same table with condition column in table-1 less than column in table-2.
SQL:
SELECT *
FROM Course c1, Course c2
WHERE c1.CourseCapacity < c2.CourseCapacity;
Results:
Above result, show to us that capacity of right part is less than left part. And the highest value of right part is the second highest of the table (MPP ~ 13). GOT IT!
So, the next step is modify the previous value with add TOP and ORDER BY clauses. The objective is do ORDER BY the above result according to right part of column CourseCapacity and then select the TOP 1 row.
SQL:
SELECT TOP 1 c1.*
FROM Course c1, Course c2
WHERE c1.CourseCapacity < c2.CourseCapacity
ORDER BY c1.CourseCapacity DESC;
Case closed
No comments:
Post a Comment