SQL Server Retrieve latest exam rows for each student

ktecyv1j 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(153)

Student with Registraion No="A1" appeared in 1st year in exam "1" and then appeared in exam "2"

I want a query that return the sum of Tmarks for "A1" of exam "2"

select * from table1

1|   A1| istYear | 100 |
   1|   A1| istYear | 100 |
   1|   A1| istYear | 50  |
 # 1|   A1| 2ndYear | 100 |
 # 1|   A1| 2ndYear | 100 |
 # 2|   A1| istYear | 100 |
 # 2|   A1| istYear | 100 |
 # 2|   A1| istYear | 50  |

select Regno, iYr, sYr
    from 
        (select 
             Regno,
             sum(case year1 when 'istYear' then tmarks end) as iyr, 
             sum(case year1 when '2ndYear' then tmarks end)  as syr
         from 
             Table1 
         group by 
             regno) as p1

# Regno  iYr    sYr
    ---------------------
      A1    | 500|  200
   ---------------------

I want a query that returns:

# Regno  iYr    sYr
 ---------------------
  A1    | 250  |  200
  ----------------------

The student with Registration No. "A1" appeared in Exam Number 1 and 2 in "Ist year".

I want a query that make the sum of Tmarks for student with Registration No "A1" that appear in the last Exam ie Exam 2.

sql-server

来源：https://stackoverflow.com/questions/76711086/retrieve-latest-exam-rows-for-each-student

1条答案

按热度按时间

aemubtdh1#

You want the rows with the greatest exam number per regno and year. You get these with DENSE_RANK giving the newest rows rank #1.

with newest as
(
  select top(1) with ties *
  from table1
  order by dense_rank() over (partition by regno, year1 order by exam desc)
)
select 
  regno,
  sum(case year1 when 'istYear' then tmarks end) as iyr, 
  sum(case year1 when '2ndYear' then tmarks end) as syr
from newest
group by regno
order by regno;

TOP (1) WITH TIES is propriatary to SQL Server. In standard SQL this is FETCH FIRST ROW WITH TIES .

You can instead find the newest rows in two steps, as suggested by Panagiotis Kanavos, as follows:

with ranked as
(
  select
    table1.*,
    dense_rank() over (partition by regno, year1 order by exam desc) as rnk
  from table1
)
, newest as
(
  select *
  from ranked
  where rnk = 1
)
select ...
from newest ...

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Retrieve latest exam rows for each student

1条答案

相关问题

热门标签

最新问答