September 27, 2012 at 5:15 am
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?
September 27, 2012 at 5:21 am
Is there any input parameter for Query?
September 27, 2012 at 5:30 am
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)
September 27, 2012 at 5:37 am
Please
September 27, 2012 at 5:38 am
;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
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
September 27, 2012 at 5:39 am
In future will be careful not to tread on toes!
September 27, 2012 at 5:43 am
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!
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
September 27, 2012 at 5:44 am
My bad! Mental note added. ^^.
September 27, 2012 at 5:47 am
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.
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
September 27, 2012 at 5:56 am
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?
September 27, 2012 at 6:11 am
diptidjadhav (9/27/2012)
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?
It's customary to post your chosen solution, diptidjadhav.
As to whether or not ROW_NUMBER() affects performance, look at the execution plan of the query, then comment out the ROW_NUMBER() output column and look at it again. What do you reckon the answer might be? What operator exists in the plan when you use ROW_NUMBER()?
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply