Get data from Select top

Question

I already had a question answered on my previous problem Select top using SQL Server returns different output than select *

I want to get select top n data from a database based on alphabetical & numbering format. The output must order by alphabet first and number after that.

When I try to get all data (select *), I get the correct output:

select nocust, share 
from TB_STOCK
where share = ’BBCA’ 
  and concat(share, nocust) < ‘ZZZZZZZZ’
order by 
    case when nocust like ‘[a-z]%’ then 0 else 1 end, nocust


nocust | share
-------+--------
a522   | BBCA
b454   | BBCA
k007   | BBCA
p430   | BBCA
q797   | BBCA
s441   | BBCA
s892   | BBCA
u648   | BBCA
v107   | BBCA
4211   | BBCA
6469   | BBCA
6751   | BBCA

when I try to select top n (ex : top 5), I got the right data too :

select top 5 nocust, share 
from TB_STOCK
where share = ’BBCA’ 
  and concat(share, nocust) < ‘ZZZZZZZZ’
order by 
    case when nocust like ‘[a-z]%’ then 0 else 1 end, nocust

nocust | share
-------+--------
a522   | BBCA
b454   | BBCA
k007   | BBCA
p430   | BBCA
q797   | BBCA

The problem is when i try to get next top 5 based on last nocust & share on previous top 5 data (concat(share, nocust) < 'ZZZZq797')) it return wrong expected data :

select top 5 nocust, share 
from TB_STOCK
where share = ’BBCA’ 
and concat(share, nocust) < ‘ZZZZq797’
order by 
case when nocust like ‘[a-z]%’ then 0 else 1 end, nocust

nocust | share
-------+--------
a522   | BBCA
b454   | BBCA
k007   | BBCA
p430   | BBCA
q797   | BBCA

it should return :

nocust | share
-------+--------
s441   | BBCA
s892   | BBCA
u648   | BBCA
v107   | BBCA
4211   | BBCA

I expect the mistake is somewhere between the concat and order by, can someone tell me how to get the right top 5.

Answer 1

You should show complete query.

Also in your query this thing is not clear to me and concat(share, nocust) < ‘ZZZZZZZZ’. I am not getting what type of data you have and what it will return or what is your expected output.

I think I will create Indexed view in such scenario.

CREATE VIEW dbo.vStockView
WITH SCHEMABINDING
AS
    select  nocust, [share] ,
 0  SortCol
from TB_STOCK
where 
--  share = ’BBCA’ 
--  and concat(share, nocust) < ‘ZZZZZZZZ’ 
 -- and 
  isnumeric(nocust)=0

  union all

  select  nocust, [share] ,
 1  SortCol
from TB_STOCK
where 
  isnumeric(nocust)=1

GO

Notice what I have commented where condition because that is not require in view.It can be implemented in Proc.

I do not have complete info about your query,table .

So below Index may not be as good.

CREATE NONCLUSTERED INDEX CX_vStockView
  ON dbo.vStockView(SortCol); 
GO

Now you create new proc like this,

Create Proc spGETStock
@share varchar(30)
@PageNumber int,
@RowspPage int=30
as

BEGIN
set nocount on

select nocust,[share] 
--,count(*)over() as TotalRecords
from vStockView
where share = @share
ORDER BY SortCol
OFFSET ((@PageNumber - 1) * @RowspPage) ROWS
FETCH NEXT @RowspPage ROWS ONLY;

End

Or you can implement Paging using Row_Number in Sql server 2008 and below.

I am sure it will improve performance significantly.

Answer 2

I'm not sure if there's a built-in function to get row ranges, but you can always use ROW_NUMBER:

select nocust, share
FROM (
  select nocust, share, 
    ROW_NUMBER() OVER(
      ORDER BY case when nocust like ‘[a-z]%’ then 0 else 1 end, nocust
    ) AS RowNum -- Assign rows "row numbers" based on `ORDER BY`
  from TB_STOCK
  where share = ’BBCA’ 
    and concat(share, nocust) < ‘ZZZZZZZZ’
) src
WHERE RowNum BETWEEN <start_row_num> AND <end_row_num> -- Get specified row range
order by 
  case when nocust like ‘[a-z]%’ then 0 else 1 end, nocust -- Not sure if this is needed

This will assign "row numbers" to each row based on your ORDER BY and then return only the range of rows you specify in the WHERE clause.