6

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!!!!

user2112420
  • 955
  • 5
  • 11
  • 26

3 Answers3

10

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.

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
0

Try this:

SELECT DISTINCT T.StudyID, T.Year, MIN(T.Access1), MIN(T.Access2), MIN(T.Access3)
FROM myTable T
WHERE T.Year = (SELECT MAX(T2.Year) FROM myTable T2 WHERE T2.StudyID = T.StudyID)
GROUP BY T.StudyID

Its giving the result you wanted in SQLite, but perhaps in SQL-Server needs some alias I'm not sure. Can't test it right now.

Pinx0
  • 1,248
  • 17
  • 28
  • I think you'll need to add T.Year to the GROUP BY list. – Tab Alleman Feb 27 '15 at 13:14
  • 1
    the group by should be `T.StudyID, T.Year` and the DISTINCT clause is unnecessary. The group by on sqlserver will be mandatory in this case – Jorge Campos Feb 27 '15 at 13:16
  • The distinct clause isn't unnecesary, it will do the same job as grouping by year in this case. – Pinx0 Feb 27 '15 at 13:46
  • when using GROUP BY, distinct is 100 % redundant. It is not possible to get duplicate rows when using GROUP BY – t-clausen.dk Feb 28 '15 at 09:10
  • @t-clausen.dk But I wasn't grouping by year, so if there were 2 years with the same year = MAx(year), it will generate 2 exactly equal rows. It is true though, that sql server won't admit that query because Year isn't grouped nor MAXed, MINed or SUMed, but SQLite does, and works. – Pinx0 Feb 28 '15 at 11:25
0

This is giving the answer you want

SELECT DISTINCT T.StudyID, T.Year, MIN(T.Access1) as Access1, MIN(T.Access2) as Access2, MIN(T.Access3) as Access3
FROM T T
WHERE T.Year = (SELECT MAX(T2.Year) FROM T T2 WHERE StudyID = T.StudyID)
GROUP BY T.StudyID, T.Year
Order by 1
tkanzakic
  • 5,499
  • 16
  • 34
  • 41
bonzo
  • 1
  • 1
  • I dont have the 50 reputation to comment on Pinx0, I just modified his query and posted as a separate post – bonzo Feb 27 '15 at 13:31