Link to home
Start Free TrialLog in
Avatar of Paulmc999
Paulmc999

asked on

SELECT NEXT 5 Names FROM DB

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?
Avatar of Imran Javed Zia
Imran Javed Zia
Flag of Pakistan image

Select top 5 surname From tbl
Order by surname
Avatar of Paulmc999
Paulmc999

ASKER

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.
select top 5 surname from table
where surname > @surname
order by surname
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

ASKER CERTIFIED SOLUTION
Avatar of Imran Javed Zia
Imran Javed Zia
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>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.
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
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
acperkins I think the question was very clear and simple
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
Made mme see that i was mistakingly using double quotes instead of single quotes
>>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.
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.
>>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.
Well acperkins i guess u r a genius so i must be wrong LOL