SQL Server Group By Query Select first row each group

user2112420 Published at Dev

user2112420

I am trying to do this query. This is what I have.

My table is: Table

StudyID FacultyID Year    Access1   Access2    Access3
1          1       2014       4        8          5
1          2       2014       8        4          7
1          1       2013       5        4          4
2          3       2014       4        6          5
2          5       2013       5        8         10
2          4       2014       5        5          7
3          7       2013       9        4          7

I want to group by StudyID and Year and get the minimum value of each field Access1 Access2 and Access3 and show only the last year, I mean for each group the first row. Here is the Result.

StudyID  Year    Access1   Access2    Access3
1        2014       4        4          5
2        2014       4        5          5
3        2013       9        4          7

This is my Query:

SELECT DISTINCT T.StudyID, T.Year, MIN(T.Access1), MIN(T.Access2), MIN(T.Access3)
FROM T
GROUP BY T.StudyID, T.Year
ORDER BY T.StudyID, T.Year DESC

I also tried with this one.

 ;WITH MyQuery AS (     SELECT DISTINCT T.StudyID, T.Year, MIN(T.Access1), MIN(T.Access2), MIN(T.Access3),ROW_NUMBER() OVER (PARTITION BY T.StudyID, T.Year ORDER BY T.StudyID, T.Year DESC) AS rownumber
    FROM T  GROUP BY T.StudyID, T.Year      ORDER BY T.StudyID , T.Year DESC ) SELECT * FROM MyQuery WHERE rownumber = 1

Any success, I know I am missing something...but dont know what? Thanks in advance!!!!

Giorgos Betsos

You can GROUP BY StudyID, Year and then in an outer query select the first row from each StudyID, Year group:

SELECT StudyID, Year, minAccess1, minAccess2, minAccess3
FROM (
   SELECT StudyID, Year, min(Access1) minAccess1, min(Access2) minAccess2,
          min(Access3) minAccess3, 
          ROW_NUMBER() OVER (PARTITION BY StudyID ORDER BY Year DESC) AS rn 
   FROM mytable
   GROUP BY StudyID, Year ) t
WHERE t.rn = 1

ROW_NUMBER is used to assign an ordering number to each StudyID group according to Year values. The row with the maximum Year value is assigned a rn = 1.

Collected from the Internet

Please contact [email protected] to delete if infringement.