Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# SQL TOP 1 for 3 difference dates

Posted on 2011-09-05
Medium Priority
334 Views
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';
0
Question by:doramail05
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 143

Expert Comment

ID: 36483351
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

LVL 12

Accepted Solution

Paul_Harris_Fusion earned 2000 total points
ID: 36483385
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

LVL 1

Author Comment

ID: 36483522
should be select top 1 for each threes,
putting  rn <= 1;

0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
###### Suggested Courses
Course of the Month10 days, left to enroll