Query help

• mamzy.rahman

  SSC Enthusiast

  Points: 120

  More actions

  June 6, 2013 at 7:58 am

  #274766

  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 !!!!!

• Sean Lange

  SSC Guru

  Points: 286601

  More actions

  June 6, 2013 at 8:43 am

  #1621768

  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/