Avatar of bmanmike39
bmanmike39 asked on

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
C#ASP.NETSQL

Avatar of undefined
Last Comment
bmanmike39

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

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

ASKER CERTIFIED SOLUTION
Louis01

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
PortletPaul

>>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.
ASKER
bmanmike39

Thanks
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23