CODEWITHSUNDEEP
sqlpostgresql
Dhairya Lakhera
Dhairya Lakhera

Reputation: 4818

select the highest record between two table

I have two table. One table contains graduation records and the second table contains post graduation records. A candidate must have graduation, but it is not necessarily to have post graduation.

My question is to select the post graduation record if the candidate has post graduation else only graduation.

table 1 graduation_table

rollno | degree | division
--------------------------
001    | B.tech | 1st
002    | B.sc   | 1st
003    | BA     | 1st

table 2 postgraduation_table

rollno | degree | division
--------------------------
002    | M.sc   | 1st

the result must be

rollno | degree | division
--------------------------
001    | B.tech | 1st
002    | M.sc   | 1st
003    | BA     | 1st

Upvotes: 0

Views: 51

Answers (4)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125574

select
    rollno,
    case when p.degree is null then g.degree else p.degree end as degree,
    case when p.division is null then g.division else p.division end as division
from
    grad g
    left join
    post p using (rollno)

Or better as suggested in the comments:

select
    rollno,
    coalesce (p.degree, g.degree) as degree,
    coalesce (p.division, g.division) as division
from
    grad g
    left join
    post p using (rollno)

Upvotes: 1

user330315
user330315

Reputation:

You want all rows from graduation_table which do not have a row in postgraduation_table plus those in postgraduation_table. This can be expressed with a not exists and union query:

select gt.rollno, gt.degree, gt.division
from graduation_table gt
where not exists (select * 
                  from postgraduation_table pg
                  where pg.rollno = gt.rollno)

union all

select rollno, degree, division
from postgraduation_table
order by rollno;

Online example: http://rextester.com/IFCQR67320

Upvotes: 1

Hervé Piedvache
Hervé Piedvache

Reputation: 798

This should make your needs :

SELECT dg.rollno, CASE WHEN pg IS NOT NULL THEN pg.degree ELSE gd.degree END AS degree, dg.division
  FROM graduation_table AS dg
  LEFT OUTER JOIN postgraduation_table AS pg USING (rollno)
 GROUP BY dg.rollno, dg.division;

Hope this help.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

Take a union of both tables, and introduce a position column, to rank the relative importance of the two tables. The postgraduate table has a pos value of 1, and the graduate table has a value of 2. Then, apply ROW_NUMBER() over this union query and assign a row number to each rollno group of records (presumed to be either one or at most two records). Finally, perform one more outer subquery to retain the most important record, postgraduate first, graduate second.

SELECT rollno, degree, division
FROM
(
    SELECT
        rollno, degree, division,
        ROW_NUMBER() OVER (PARTITION BY rollno ORDER BY pos) rn
    FROM
    (
        SELECT p.*, 1 AS pos p FROM postgraduation_table
        UNION ALL
        SELECT p.*, 2 FROM graduation_table p
    ) t
) t
WHERE t.rn = 1;

Upvotes: 0

Related Questions