CODEWITHSUNDEEP
sqlsql-server
chamara
chamara

Reputation: 12711

Select Max with groupby

I have the following table.

enter image description here

I need to select SemesterID,AcadamiYear,AcademicSemester of the record with highest Academic year and Academic semester of the year 2015

Expected output is

2013 1 2

I tried the following query but it returns both of the records

select MAX(AcadamiYear) as Year,
  MAX(AcadamicSemester) as Semester
  ,SemesterID
  from
  tblSemesterRegistration 
  where [IntakeYear]='2015'
  Group by SemesterID

Upvotes: 1

Views: 58

Answers (4)

John Bell
John Bell

Reputation: 2350

SELECT MAX(AcadamiYear) AS Year,
    MAX(AcademicSemester) AS Semester,
    MAX(SemesterID) AS SemesterID
FROM tblSemesterRegistration 
WHERE [IntakeYear] = '2015'
GROUP BY IntakeYear

Upvotes: 0

Patrick Hofman
Patrick Hofman

Reputation: 156928

If SemesterID is the primary key, grouping on it will always yield all rows (since it is always unique).

I guess you mean to find back that semester id with the parameters set:

select r.*
from   tblSemesterRegistration r
join   ( select max(AcadamiYear) as Year
         ,      max(AcadamicSemester) as Semester
         from   tblSemesterRegistration 
         where  [IntakeYear]='2015'
       ) m
on     r.acadamiyear = m.year
and    r.acadamicsemester = m.semester

Upvotes: 0

Joël Salamin
Joël Salamin

Reputation: 3576

This is the query you're looking for:

SELECT SR.*
FROM tblSemesterRegistration SR
INNER JOIN (SELECT MAX(SR2.AcadamiYear) AS [AcadamiYear]
                  ,MAX(SR2.AcadamicSemester) AS [AcadamicSemester]
                  ,IntakeYear
            FROM tblSemesterRegistration SR2
            GROUP BY SR2.IntageYear) T ON T.AcadamiYear = SR.AcadamiYear
                                         AND T.AcadamicSemester = SR.AcadamicSemester
                                         AND T.IntakeYear = SR.IntakeYear
WHERE SR.IntakeYear = '2015'

Hope this will help you.

Upvotes: 1

bummi
bummi

Reputation: 27367

Since you are searching for a single record you might use TOP 1, ordered by your intend

select TOP 1 *
  from
  tblSemesterRegistration 
  where [IntakeYear]='2015'
  Order by AcadamiYear DESC, AcadamicSemester DESC

Upvotes: 4

Related Questions