Possible "limit n/select top n" limitation within subquery workaround

Question

I have the following case (the example I am demonstrating is on a dummy domain because for NDA reasons etc.).

I have access to a production DB holding student data in the following shape:

Student_ID  Course_Name Total_Average_Grade
1           Maths       19,1
1           Physics     0
1           Biology     0
2           Physics     18,5
2           Chemistry   0
3           Maths       19,4
3           Literature  0
3           Physics     0

I want to retrieve say the 10 students with the highest total average grade, and for those 10 retrieve the whole list of courses they took. I want to run this query on a monthly basis.

For example, on the above table, if I wanted to select the top 2 students, the output would be:

Student_ID  Course_Name Total_Average_Grade
3           Maths       19,4
3           Physics     0
3           Biology     0
1           Maths       19,1
1           Literature  0
1           Physics     0

Note that I do not have rights to create either views or stored procedures on this database , which would be an easy way to overcome the limitation of not being able to perform subqueries on a (select top n / limit n) selection (for example, I would be able to automatically create a monthly view which I would use to join with a plain select top 10 averages etc.).

Is there any elegant solution on this task?

For the record, I am using Sybase 15.4 as client to access the Sybase IQ production databse.

check output plz. student 1 getting 19,4 ? but in input table its 19,1 ? — Mr. Bhosale, Jan 16 '17 at 09:04
in output , Maths 19,1 ? can you describe this. its total is 20. which is less than studentid 2 and 3. — Mr. Bhosale, Jan 16 '17 at 10:11
The total average of all lessons is stored in the first row of each student. Hence 19.1, 19.4, etc. is the average over all lessons, but it is note repeated in every line for each student but saved on the first row only. The individual mark for each lesson is unknown and irrelevant. — rpd, Jan 16 '17 at 10:15

score 1 · Accepted Answer · answered Jan 16 '17 at 09:21

1

I think you can use a query like this:

select *
from (
    select *,
        row_number() over (partition by Course_Name order by Total_Average_Grade desc) as rn
    from yourTable) t
where t < 11;

answered Jan 16 '17 at 09:21

shA.t

16,580
5
54
111

Both work but first solution is much more elegant. I am kinda new to analytic SQL functions and logic, this task and similar seem much easier than thought when using analytic functions. – rpd Jan 16 '17 at 11:03

score 1 · Answer 2 · answered Jan 16 '17 at 10:23

Check below query which successfully runs oN SQL Server.

        DECLARE @student TABLE(
                Student_ID INT,        
                Course_Name VARCHAR(100),
                Total_Average_Grade VARCHAR(100)
        )

        INSERT INTO @student 
        select 1, 'Maths  '     ,'19.1' union
        select 1, 'Physics'     ,'0'union
        select 1, 'Biology'     ,'0'union
        select 2, 'Physics'     ,'18.5'union
        select 2, 'Chemistry'   ,'0'union
        select 3, 'Maths'       ,'19.4'union
        select 3, 'Literature'  ,'0'union
        select 3, 'Physics'     ,'0'


        ;WITH ValList AS(
                SELECT  Student_ID,Course_Name, Case when Total_Average_Grade like '%,%' then

                        CAST(LEFT(Total_Average_Grade,PATINDEX('%,%', Total_Average_Grade) - 1) AS float) 
                        else Total_Average_Grade end as
                        Total_Average_Grade,
                        RIGHT(Total_Average_Grade,LEN(Total_Average_Grade) - PATINDEX('%,%', Total_Average_Grade)) Remainder

                FROM    @student
                UNION ALL
                SELECT  Student_ID,Course_Name,
                        CAST(LEFT(Remainder,CASE WHEN PATINDEX('%,%', Remainder) = 0 THEN LEN(Remainder) ELSE PATINDEX('%,%', Remainder) - 1 END) AS float) Total_Average_Grade,
                        RIGHT(Remainder,CASE WHEN PATINDEX('%,%', Remainder) = 0 THEN 0 ELSE LEN(Remainder) - PATINDEX('%,%', Remainder) END) Remainder
                FROM    ValList
                WHERE   LEN(Remainder) > 0

        )


        select SS.* from @student SS  
        join 
        (
        SELECT  top 2 v.Student_ID  --  use TOP 10 for 10 records
        --,s.Course_Name,s.Total_Average_Grade,SUM(v.Total_Average_Grade) as Avg,
        ,row_number() over( order by SUM(v.Total_Average_Grade) desc) rnk
        FROM    ValList V
        join @student S on s.Student_ID=V.Student_ID and s.Course_Name=V.Course_Name
        GROUP BY v.Student_ID
        )AA on AA.Student_ID=SS.Student_ID
        order by rnk asc,Total_Average_Grade desc

OutPut :

Possible "limit n/select top n" limitation within subquery workaround

2 Answers2