how to select top 7th and 10th marks from two tables

Question

I have t1 table with roll and name and table t2 have roll and marks.
now i have to select top of 7th marks in first row and top 10th marks in 2nd row and also remaining records will print after 3rd row.
what i have to do.
i have use join but i dont know how to use where and order by with join. and also how to select multiple queries to show the data simultanously

Muralidharan Deenathayalan · Answer

Do a union in sql. //Returns top 7th record SELECT roll mark FROM ( SELECT roll mark RANK () OVER (ORDER BY mark DESC) rank1 FROM emp ) WHERE rank1 = 7 union //Returns top 10th record SELECT roll mark FROM ( SELECT roll mark RANK () OVER (ORDER BY mark DESC) rank1 FROM emp ) WHERE rank1 = 10 union //returns all the records except 7th and 10th record SELECT roll mark FROM ( SELECT roll mark RANK () OVER (ORDER BY mark DESC) rank1 FROM emp ) WHERE rank1 != 7 rank1 != 10 I have not tested this.

Iftikar Hussain · Answer

Hi Try like this select B.name A.rollmarkscase RowNo when 7 then -1 when 10 then 0 else RowNo end rn from ( SELECT roll marks ROW_NUMBER() OVER (ORDER BY marks DESC) RowNo FROM Table2 ) as A INNER JOIN Table1 B ON B.Roll=A.Roll order by rn Regards Iftikar

Khargesh Rajput · Answer

sir i have tried this but i have to show result from two diff tables and hace to display all result in single query

Muralidharan Deenathayalan · Answer

//Returns top 7th record SELECT roll mark FROM ( SELECT roll mark RANK () OVER (ORDER BY mark DESC) rank1 FROM emp ) WHERE rank1 = 7 //Returns top 10th record SELECT roll mark FROM ( SELECT roll mark RANK () OVER (ORDER BY mark DESC) rank1 FROM emp ) WHERE rank1 = 10 //returns all the records except 7th and 10th record SELECT roll mark FROM ( SELECT roll mark RANK () OVER (ORDER BY mark DESC) rank1 FROM emp ) WHERE rank1 != 7 rank1 != 10 I have not tested this.