June 6, 2013 at 7:58 am
hi guys need help with a query please. i have a tablethat looks like this;
id name aim startdate
1 kev maths 31/10/2010
1 kev english 01/11/2010
2 trev science 01/09/2010
2 hev maths 31/10/2010
3 nev history 20/3/2010
4 mev maths 12/12/2010
5 brad english 31/ 5/2010
5 brad maths 31/10/2010
i need to find out which people have an aim at the start date 31/10/2010 as there earliest start date for their aims.
result
id name aim startdate
1 kev maths 31/10/2010
2 hev maths 31/10/2010
note how brad is not in result as he has english 31/5/2010 as his earliest start date for one of his aims.
please help !!!!!
June 6, 2013 at 8:43 am
This produces the results as stated in your post. Please notice how I created sample data in a readily consumable format. This makes it a lot easier for the people around to work on your issue. This is something you should do in the future.
--setup
if OBJECT_ID('tempdb..#Something') is not null
drop table #Something
create table #Something
(
ID int,
Name varchar(10),
aim varchar(10),
startdate datetime
)
set dateformat dmy
insert #Something
select 1, 'kev', 'maths', '31/10/2010' union all
select 1, 'kev', 'english', '01/11/2010' union all
select 2, 'trev', 'science', '01/09/2010' union all
select 2, 'hev', 'maths', '31/10/2010' union all
select 3, 'nev', 'history', '20/3/2010' union all
select 4, 'mev', 'maths', '12/12/2010' union all
select 5, 'brad', 'english', '31/5/2010' union all
select 5, 'brad', 'maths', '31/10/2010'
select * from #Something;
--solution
with MyData as
(
select ID, Name, aim, startdate, ROW_NUMBER() over (partition by Name order by startdate ) as RowNum
from #Something
)
select ID, Name, aim, startdate
from MyData
where RowNum = 1
and startdate = '2010-31-10'
order by ID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply