CODEWITHSUNDEEP
sqlpostgresql
CodyBugstein
CodyBugstein

Reputation: 23312

Subtracting one table from another in PostgreSQL

I have a table that contains student IDs and their GPA.

_____________
| sID | GPA |
-------------
|  1  | 3.7 |
|  2  | 3.9 |
|  3  | 3.6 |
|  4  | 3.7 |
|  5  | 3.1 |
|  6  | 3.9 |

I wanted to create a table that gives me only the students who have the highest, or second highest GPAs. In other words, I want this result:

_____________
| sID | GPA |
-------------
|  2  | 3.9 |
|  6  | 3.9 |
|  1  | 3.7 |
|  4  | 3.7 |

To do so, I began by writing a query that gives me all the students who match the highest score:

SELECT * 
FROM gpaTable
WHERE gpa in (
      SELECT max(gpa) FROM gpaTable)

This gives me: 

| sID | GPA |
-------------
|  2  | 3.9 |
|  6  | 3.9 |

To get the second highest as well, I want to subtract this result from the entire original table, and then repeat the query that finds the max again.

I assume it would look something like this, however, I can't get it right.

SELECT *
FROM gpaTable
WHERE gpa IN (
    SELECT * 
    FROM gpaTable
    WHERE gpa in (
          SELECT max(gpa) FROM gpaTable)
    )
 OR
    (SELECT *
     FROM gpaTable
     WHERE NOT EXISTS IN
           (SELECT * 
            FROM gpaTable
            WHERE gpa in (
            SELECT max(gpa) FROM gpaTable)
    )

In English, the query says (or should say) Give me every row that appears in the table of maximum GPAs OR in the table of maximum GPAs of the lesser GPAs

I'd really appreciate any help!

Upvotes: 1

Views: 3757

Answers (3)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726489

You can do it like this:

select *
from gpaTable
where gpa in (
  select distinct gpa
  from gpaTable
  order by gpa desc
  limit 2
)

The idea is similar to your first solution, except MAX is replaced with ordering and a LIMIT 2

Demo on sqlfiddle.

Upvotes: 1

Sirko
Sirko

Reputation: 74036

Would it not be easier to just select the top 2 entries in the subselect? You could, e.g., use LIMIT:

SELECT * 
FROM gpaTable
WHERE gpa in (SELECT DISTINCT gpa FROM gpaTable ORDER BY gpa DESC LIMIT 2) ORDER BY gpa;

Upvotes: 1

user330315
user330315

Reputation: 

SELECT sid, gpa
FROM (
   select sid, 
          gpa, 
          dense_rank() over (order by gpa desc) as rnk
   from gpaTable
) t
where rnk <= 2
order by sid;

Upvotes: 1

Related Questions