SELECT NEXT 5 Names FROM DB

Paulmc999
Paulmc999 used Ask the Experts™
on
If I have a surname in a variable, can anyone tell me how to get the next 5 surnames from a table if ordered on surname?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Imran Javed ZiaConsultant Software Engineer - .NET Architect

Commented:
Select top 5 surname From tbl
Order by surname

Author

Commented:
thanks IJZ if only it was that simple. This will give me the first 5 records in the table -  I want the 5 records that come after the surname in the variable.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
select top 5 surname from table
where surname > @surname
order by surname
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
I have tried this angelii and it says the value in @surname is not a column
try this:-

with cte as 
 (Select surname,row_number() over (order by surname) as rowid From tbl
 )
 select * from cte where rowid between 5 and 10 

Open in new window

Consultant Software Engineer - .NET Architect
Commented:
ok,
then use the following

Declare @sn VarChar(8000)
set @sn = 'abc'

Select * From tbl
Where surname >=@sn
Order by surname
Top Expert 2012

Commented:
>>I have tried this angelii and it says the value in @surname is not a column <<
You are going to have to elaborate, we cannot keep guessing.

Author

Commented:
Thanks as I told Angellii I tried this before i submitted my question and it says abc is not a column.
This was my code that gives me that error:

DECLARE @SURN VARCHAR(20)
SELECT @SURN = "Barker"
SELECT TOP 5 * FROM Person
WHERE lastnameT >= @SURN
ORDER BY LastnameT

Think it is exactly what you both are suggesting

Author

Commented:
ok I see my problem I should be using single quotes!!!!!!

thanks ijz I accept your solution cause it led me to see my mistake

Author

Commented:
acperkins I think the question was very clear and simple
Imran Javed ZiaConsultant Software Engineer - .NET Architect

Commented:
You are most welcome

you may use it as

DECLARE @SURN VARCHAR(20)
SELECT @SURN = 'Barker'
SELECT TOP 5 * FROM Person
WHERE lastnameT > @SURN
ORDER BY LastnameT

please note single qoute and WHERE lastnameT > @SURN , you may use >= or > as per requirements

Author

Commented:
Made mme see that i was mistakingly using double quotes instead of single quotes
Top Expert 2012

Commented:
>>I think the question was very clear and simple <<
The answer was more than simple, the original question I am afraid was not clear or it would not have taken this many responses.  In any case thanks for elaborating.

Author

Commented:
well i disagree I've asked 3 colleagues to read this question and tell me what they understand from it and they all got it first time without any elaboration but thanks for your time anyway. Any misunderstandings that arose were clearly because the question was not fully read first time.
Top Expert 2012

Commented:
>>Any misunderstandings that arose were clearly because the question was not fully read first time. <<
Everyone has a right to their opinion.  This thread says otherwise.

Good luck.

Author

Commented:
Well acperkins i guess u r a genius so i must be wrong LOL

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial