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

Khargesh Rajput

13y1.6k

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

13y ago
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 Hussain13y ago
Hi,
Try like this

select
B.name,
A.roll,marks,case 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 Rajput13y ago
sir i have tried this but i have to show result from two diff tables and hace to display all result in single query
13y ago
//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.