SQL ROW_NUMBER OVER AND PARTITION BY

Table Name: myTable
Columns:

-->roll_no
-->classId
-->location

SQL Statements

--Get the count of rows based on columns

  SELECT *, ROW_NUMBER()OVER(PARTITION BY roll_no  ORDER BY subject DESC) rowCnt
    FROM myTable order by rowCnt desc
 
--Get the count of rows based on columns using where clause

 SELECT *, ROW_NUMBER()OVER(PARTITION BY roll_no  ORDER BY subject DESC) rowCnt
    FROM myTable
    where myTable.roll_no='1001'
 
--Query sub query data based on where clause  

    SELECT rid FROM(
 SELECT *, ROW_NUMBER()OVER(PARTITION BY roll_no  ORDER BY subject DESC) rowCnt
    FROM myTable
    where myTable.roll_no='1001'
)X WHERE classId= 7 and upper(location) != 'USA' and rowCnt = 1