CODEWITHSUNDEEP
sql-serversql-server-2005
Venkateswarlu Avula
Venkateswarlu Avula

Reputation: 441

Finding latest month, year including the other fields

Table1 is as follows :

Name    Number  Month   Year    Fee_Paid
Ravi    1       1       2010    100
Raju    2       1       2010    200
Kumar   3       1       2010    300
Ravi    1       2       2011    100
Raju    2       2       2011    200
Kumar   3       2       2011    300
Ravi    1       3       2012    100
Raju    2       3       2012    200
Kumar   3       3       2012    300

Result Should be as follows: 

Name    Number  Month   Year    Fee_Paid
Ravi    1       3       2012    100
Raju    2       3       2012    200
Kumar   3       3       2012    300

The result should display the Latest year and Month including the Name, Number and fee_Paid in that period. Kindly help me.

Upvotes: 0

Views: 72

Answers (3)

Pittsburgh DBA
Pittsburgh DBA

Reputation: 6772

SELECT
   Details.Name,
   Details.Number,
   Details.Month,
   Details.Year,
   Details.Fee_Paid
FROM
   (
   SELECT
      Name,
      MAX(Year) AS Year,
      MAX(Month) AS Month
   ) AS MaxValues
INNER JOIN MyTable AS Details ON
   Details.Name = MaxValues.Name
   AND Details.Year = MaxValues.Year
   AND Details.Month = MaxValues.Month

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

This is a non-CTE solution:

select HighestYear.* from (
  select v1.* from visitors v1
  left join visitors v2
  on v1.number = v2.number and v1.year < v2.year
  where v2.year is null
) HighestYear
left join visitors v3
on HighestYear.number = v3.number and HighestYear.year = v3.year
and HighestYear.month < v3.month
where v3.month is null

Example with more data

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

If you want the last row for each name, regardless of which month was their last month:

;WITH x AS 
(
    SELECT Name, Number, [Month], [Year], [Fee_Paid],
      rn = ROW_NUMBER() OVER 
      (PARTITION BY Name ORDER BY [Year] DESC, [Month] DESC)
    FROM dbo.Table1
)
SELECT Name, Number, [Month], [Year], [Fee_Paid]
FROM x
WHERE rn = 1
ORDER BY Number;

If you want all the rows from the last year/month combo found, regardless of name, the query is only slightly different:

;WITH x AS 
(
    SELECT Name, Number, [Month], [Year], [Fee_Paid],
      rn = DENSE_RANK() OVER (ORDER BY [Year] DESC, [Month] DESC)
    FROM #x
)
SELECT Name, Number, [Month], [Year], [Fee_Paid]
FROM x
WHERE rn = 1
ORDER BY Number;

Both of these queries give the result you seem to be after, but one of them is probably correct only by coincidence.

Upvotes: 1

Related Questions