SQL TOP 1 for 3 difference dates

the following sql server 2008 command will produce 17 records for each dates ,  29-03-2011 (17 records), 30-03-2011 (17 records), 31-03-2011 (17 records),

trying to find a method to select TOP 1 for each of the 3 dates.
 
select smp.starttime as starttime, smp.id as smpid from StudentModulePeriod smp 
                                 INNER JOIN Lesson l ON smp.lid = l.id 
                                 INNER JOIN Module m ON smp.mid = m.id 
                                 INNER JOIN Period p ON smp.pid = p.id 
                                 INNER JOIN Student s ON smp.sid = s.id 
                                 INNER JOIN Trainer tr ON smp.trid = tr.id 
                                 WHERE DATEPART(month, smp.onmonth) = 03 AND 
                                 p.intakename = 'March 2011';

Open in new window

LVL 1
doramail05Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Paul_Harris_FusionConnect With a Mentor Commented:
You need to think about the basis for selecting the top3.

The following query uses the ROW_NUMBER function to allocate an index (rn) to each record.  We then select just those records <=3.

>  In my example the row numbering resets (to 1) whenever the day part of the onMonth value changes - this is the PARTITION BY clause.
>  The ordering is determined (unsurprisingly:))  by the ORDER BY clause.

You should tailor the call to ROW_NUMBER to implement your logic for selecting the top 3

Select * FROM
(
  select smp.starttime as starttime, smp.id as smpid
  , ROW_NUMBER() OVER (PARTITION BY DATEPART(day,smp.onmonth) ORDER BY p.id) as rn
  from StudentModulePeriod smp
  INNER JOIN Lesson l ON smp.lid = l.id
  INNER JOIN Module m ON smp.mid = m.id
  INNER JOIN Period p ON smp.pid = p.id
  INNER JOIN Student s ON smp.sid = s.id
  INNER JOIN Trainer tr ON smp.trid = tr.id
  WHERE DATEPART(month, smp.onmonth) = 03 AND
  p.intakename = 'March 2011';
) ILV
Where rn<=3
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can read this article to see suggestions/solutions ...
http://www.experts-exchange.com/A_3203.html

you shall probably use the ROW_NUMBER() partitioning clause in a inner query to get this working
0
 
doramail05Author Commented:
should be select top 1 for each threes,
putting  rn <= 1;

0
All Courses

From novice to tech pro — start learning today.