CODEWITHSUNDEEP
t-sqlsql-server-2005
RoyalSwish
RoyalSwish

Reputation: 1573

SQL - Select max week from a group

I need to be able to get a result set which shows the last teacher for a course, for which I have the following SQL query:

SELECT      
    a.acad_period, MAX(a.start_week) as start_week, 
    a.staff_code, b.aos_code, b.aos_period
FROM 
    qlsdat.dbo.sttstaff a
INNER JOIN 
    qlsdat..sttrgaos b ON a.acad_period = b.acad_period 
                       AND a.register_id = b.register_id
                       AND a.register_group = b.register_group
WHERE 
    a.acad_period = '14/15'
GROUP BY 
    a.acad_period, a.staff_code, b.aos_code, b.aos_period

However, the issue is that it returns to me the maximum start week for a teacher on that course, whereas I want the maximum start week for a course, and the teacher that happens to be teaching for that start week.

Here is a sample result set returned from the above query:

14/15   37  HKARUNATHIL A2ES        001A 
14/15   37  CSHUKLA     A2ES        001B 
14/15   37  PSEDOV      A2ES        002A 
14/15   37  BBANFIELD   A2ES        002B 
14/15   14  VKRISHNASWA A2EX        BL1 X
14/15   14  VKRISHNASWA A2EX        BL2 X
14/15   6   BODAMEKENTO ACA2        BL1 A
14/15   41  SKLER       ACA2        BL1 A
14/15   44  BODAMEKENTO ACAS        BL1 F
14/15   37  MMILLER     ARA2        BL1 C
14/15   45  MMILLER     ARAS        BL1 E
14/15   44  SHOULTON    ARAS        BL1 E

Here is an example of the problem within the result set:

14/15   10  HMALIK      MMGX        GB2F3
14/15   44  JMULLANEY   MMGX        GB2F3

In the above example I only want:

14/15   44  JMULLANEY   MMGX        GB2F3

The query produced is going to be used as a subquery in another query.

Upvotes: 0

Views: 756

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44336

This will get the row for highest start_week, however you may encounter some problems if you have data from more than 1 year, this can be resolved by putting your your field in addition to your week column in this part

row_number() over (partition by 
                     a.acad_period, b.aos_code, b.aos_period 
                   order by 
                     a.start_year desc, 
                     a.start_date desc) rn

Query:

;WITH CTE AS
(
    SELECT      
        a.acad_period, a.start_week,
        a.staff_code, b.aos_code, b.aos_period,
        row_number() over (partition by 
                             a.acad_period, b.aos_code,
                             b.aos_period 
                           order by a.start_week desc) rn
    FROM 
        qlsdat.dbo.sttstaff a
    INNER JOIN 
        qlsdat..sttrgaos b ON a.acad_period = b.acad_period 
                           AND a.register_id = b.register_id
                           AND a.register_group = b.register_group
    WHERE 
        a.acad_period = '14/15'
)
SELECT 
    acad_period, start_week,
    staff_code, aos_code, aos_period,
FROM CTE
WHERE rn = 1

Upvotes: 1

Related Questions