Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query to find out latest data of each student Expand / Collapse
Author
Message
Posted Thursday, September 27, 2012 5:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:21 AM
Points: 5, Visits: 25
I have data as below

Name--Month----Year--------Marks
A--------8--------2012--------80
A--------7--------2012--------88
A--------3--------2011--------70
B--------8--------2012--------80
B--------7--------2012--------88
B--------3--------2011--------70
C--------3--------2012--------80
C--------5--------2012--------88
C--------7--------2012--------70

I want result like this
Name----Month----Year-------Marks
A----------8--------2012--------80
B----------8--------2012--------80
C----------7--------2012--------70


I want latest mark of each student (latest means by considering year,month). Is it possible in single sql query?



Post #1365147
Posted Thursday, September 27, 2012 5:21 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 7:16 AM
Points: 3,931, Visits: 1,102
Is there any input parameter for Query?


Post #1365149
Posted Thursday, September 27, 2012 5:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:21 AM
Points: 5, Visits: 25
Hi vyas,
The query provided by you gives an error. you can run your query try with below table structure & data.

create table tblStudent
(
Name varchar(100),
Month int,
Year int,
Mark int
)

insert into tblStudent(Name,Month,Year,Mark) values ('A',8,2012,80)
insert into tblStudent(Name,Month,Year,Mark) values ('A',7,2012,88)
insert into tblStudent(Name,Month,Year,Mark) values ('A',3,2011,70)
insert into tblStudent(Name,Month,Year,Mark) values ('B',8,2012,80)
insert into tblStudent(Name,Month,Year,Mark) values ('B',7,2012,88)
insert into tblStudent(Name,Month,Year,Mark) values ('B',3,2011,70)
insert into tblStudent(Name,Month,Year,Mark) values ('C',3,2012,80)
insert into tblStudent(Name,Month,Year,Mark) values ('C',5,2012,88)
insert into tblStudent(Name,Month,Year,Mark) values ('C',7,2012,70)
Post #1365154
Posted Thursday, September 27, 2012 5:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:21 AM
Points: 5, Visits: 25
Please
Post #1365156
Posted Thursday, September 27, 2012 5:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:43 AM
Points: 6,890, Visits: 14,254
;WITH CTE AS (
SELECT
Name,
[Month],
[Year],
Mark,
rn = ROW_NUMBER() OVER(PARTITION BY Name ORDER BY <<Something>>)
FROM tblStudent
)
SELECT
Name,
[Month],
[Year],
Mark
FROM CTE
WHERE rn = 1



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1365157
Posted Thursday, September 27, 2012 5:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 1:29 AM
Points: 110, Visits: 135
In future will be careful not to tread on toes!
Post #1365158
Posted Thursday, September 27, 2012 5:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:43 AM
Points: 6,890, Visits: 14,254
Jinxsee (9/27/2012)
select 
VALUE, YEAR, MONTH, MARK from
(select VALUE, YEAR, MONTH, MARK, ROW_NUMBER() OVER(PARTITION BY VALUE ORDER by YEAR desc, MONTH desc) as rownumber from TABLE
group by VALUE,YEAR, MONTH, MARK) as a
where a.rownumber = 1




Excellent - but it's customary on ssc to encourage folks to put some effort into their homework. You may be sitting next to diptidjadhav in a couple of years' time, knowing that he used to post his homework on ssc then go to the pub whilst us folks here came up with all his answers!


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1365163
Posted Thursday, September 27, 2012 5:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 1:29 AM
Points: 110, Visits: 135
My bad! Mental note added. ^^.
Post #1365164
Posted Thursday, September 27, 2012 5:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:43 AM
Points: 6,890, Visits: 14,254
Jinxsee (9/27/2012)
My bad! Mental note added. ^^.


No worries - now how about fixing your code so that it runs against the OP's sample data? You don't need the GROUP BY.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1365165
Posted Thursday, September 27, 2012 5:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:21 AM
Points: 5, Visits: 25
Hey thanks Jinxsee and ChrisM@Work. Your solution works perfect. Thanks a lot.
One more question for Jinxsee, Suppose if we are using row_number() for a huge records it will minimize performance?
Post #1365169
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse