CODEWITHSUNDEEP
sqlsql-server
zkanoca
zkanoca

Reputation: 9918

How to select latest year's records using sql

I am using SQL Server. I want to list the weekly information of a tutorial.

SELECT 
       tx.Lab, t.weekNo 
FROM 
       TutorialX tx 
INNER JOIN 
       Tutorial t 
ON 
       tx.id = t.id 
WHERE 
       t.tutID = @tutID AND 
       t.ProgramID = @pID AND 
       tx.Culture = @culture      
ORDER BY 
       t.WeekNo

Year field is in the table Tutorial.

This query brings me the records for all years. There are 14~16 records for each year. There will be 14~16 more new records for each year but I want to get only the biggest/latest year's records. For today, I want to get 2012's 14~16 records, next Year 2013's...

I have tried this but the result is the same using this query:

SELECT 
       tx.Lab, t.weekNo 
FROM 
       TutorialX tx 
INNER JOIN 
       Tutorial t 
ON 
       tx.id = t.id 
WHERE 
       t.tutID = @tutID AND 
       t.ProgramID = @pID AND 
       tx.Culture = @culture AND
       t.year IN ( SELECT MAX(year) AS y FROM Tutorial GROUP BY year)
ORDER BY 
       t.WeekNo

Upvotes: 0

Views: 96

Answers (2)

zkanoca
zkanoca

Reputation: 9918

I have solved the problem removing GROUP BY year part in the inner query. 

SELECT 
    t.weekNo, tx.Lab 
FROM 
    TutorialX tx 
INNER JOIN 
    Tutorial t 
ON 
    tx.id = t.id 
WHERE 
    t.tutID = @tid AND 
    t.ProgramID = @pid AND 
    tx.Culture = @culture AND 
    t.year IN (SELECT MAX(year) AS y FROM Tutorial) 
ORDER BY 
    t.weekNo

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

If you are using SQL Server 2005+, you can use the ranking function ROW_NUMBER() OVER(PARTITION BY ...) to do so:

WITH CTE
AS
(
    SELECT  tx.Lab,
      ROW_NUMBER() OVER(PARTITION BY year ORDER BY t.weekno) AS RN 
    FROM TutorialX tx 
    INNER JOIN Tutorial t ON tx.id = t.id 
    WHERE t.tutID = @tutID 
      AND t.ProgramID = @pID 
      AND tx.Culture = @culture      
)
SELECT Lab
FROM CTE
WHERE RN = 1;

Upvotes: 1

Related Questions