Is there a way to ignore the first 4 records and select the second 4 records in a database?

Is there a way to ignore the first 4 records and select the second 4 records in a database.

This is what i have:
SELECT courseName, description, date from course WHERE date < dateNow
bmanmike39Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you clarify the database you use, we can clarify the syntax to use.
presuming the database is ms sql server, you can do it like this:
http://technet.microsoft.com/en-us/library/ms186734.aspx
select  courseName, description, date
 from  (
SELECT courseName, description, date 
  , row_number() over ( order by date desc ) rn
 from course WHERE date < getdate() 
 ) sq
where sq.rn between 5 and 8 

Open in new window


if you wanted the 4 records per courseName, we add simply a partition by in the rownumber function:
select  courseName, description, date
 from  (
SELECT courseName, description, date 
  , row_number() over ( partition by courseName order by date desc ) rn
 from course WHERE date < getdate() 
 ) sq
where sq.rn between 5 and 8 

Open in new window

0
Louis01Commented:
Assuming you're using SQL Server:

--Prepare some test data
declare @Course table (courseName varchar(50), courseDate date);
insert into @Course values ('Course 1', '30-Aug-2013');
insert into @Course values ('Course 2', '31-Aug-2013');
insert into @Course values ('Course 3', '01-Sep-2013');
insert into @Course values ('Course 4', '02-Sep-2013');
insert into @Course values ('Course 5', '03-Sep-2013');
insert into @Course values ('Course 6', '04-Sep-2013');
insert into @Course values ('Course 7', '05-Sep-2013');
insert into @Course values ('Course 8', '06-Sep-2013');
insert into @Course values ('Course 9', '07-Sep-2013');
insert into @Course values ('Course 10', '08-Sep-2013');
insert into @Course values ('Course 11', '09-Sep-2013');
insert into @Course values ('Course 12', '10-Sep-2013');

--ignore the first 4 records and select the second 4 records in a database
select TOP 4 
       t1.courseName  
     , t1.courseDate
  from (select c.courseName  
             , c.courseDate
             , ROW_NUMBER() OVER(ORDER BY c.courseDate) as rowNumber
          from @Course c     
         where c.courseDate < GETDATE()
        ) t1
 where t1.rowNumber > 4
 order by courseDate 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
>>if you clarify the database you use, we can clarify the syntax to use.
It would certainly be helpful if we knew which database (or databases) you need this for as the approach and/or syntax can differ.

row_number() is not available in MySQL for example.
0
bmanmike39Author Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.