Find Second Highest Value in a Table Without Sub-Query

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;

Result:

Case closed

Represent a Superclass/Subclass Relationship

For each superclass/ subclass relationship in ER-diagrams, we identify the superclass entity as parent entity and the subclass entity as the child entity.

There are various options on how to represent those relationship as one or more relations. The selection of the most appropriate option is dependent on a number of factors such as the disjointness and participation constraints on that relationship.

For example: Staff can have one or more position on the same time in the company. The available positions are Staff, Manager, Salesman, Interviewer, and Head of Branch. It’s also possible in the future, company adding new position.

There are many solution for establish that relationship:

1. Create only single relation with one or more discriminators to distinguish the type of each tuple.
2. Create two relations where one relation for superclass and one relation for all subclasses with one or more discriminators to distinguish the type of each tuple.
3. Create many relations where one relation for each combined superclass/ subclass.
4. Create many relations where one relations for superclass and one for each subclass.

Here, I’m want to offering another idea for establish that relationship, “Bitwise Operation Attribute”. In computer programming, a bitwise operation operates on one or two bit patterns or binary numerals at the level of their individual bits.

In this case, we use AND bitwise operation.

Table Position

Table Employee

How to getting the position for employee S102 ?

• Do bitwise operations in T-SQL for getting the list of position;
• Do natural join.

T-SQL:
SELECT * FROM Employee e, Position p
WHERE (e.position & p.posValue) > 0 AND e.empID = ‘S102’
ORDER BY p.posValue;