1

I know it can do by view, but I dont want use views. first some rows selecting as below:

select *   from 
(
    select top(3 + 6 - 1) * from mytable
    except
    select top(6 - 1) * from mytable
) as newtable
/*then select from selected rows as below:*/
select id,name from newtable where condition

how can I do this without view?

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
Robert
  • 45
  • 6
  • Maybe you could see this thread: http://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table – Johnny Jul 31 '14 at 01:14
  • Why such strange hard coded calculations? If they are constants why not make them as such. The bigger issue is that you are using TOP but there is no order by. Effectively this query is the same as TOP 3. What is this query trying to do? – Sean Lange Jul 31 '14 at 13:18
  • @SeanLange that query,select row m to n. – Robert Jul 31 '14 at 13:35
  • GO is not a t-sql statement. It is the default batch terminator in SSMS. Not really sure what that has to do with the issue though. – Sean Lange Jul 31 '14 at 13:35
  • I can only assume you pass those values in a parameters or you build this as a pass through query. Still with no order by there is no way to know what rows you will get back. – Sean Lange Jul 31 '14 at 13:36
  • yes i send query from webform without SP. i searched before to find best way about select row m to n. it is best. – Robert Jul 31 '14 at 13:40

2 Answers2

0

You haven't explained very well but perhaps you want a CTE?

With MyCTE AS (
    select *   from 
    (
        select top(3 + 6 - 1) * from mytable
        except
        select top(6 - 1) * from mytable
    ) as newtable
);

select id,name from MyCTE where condition
Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • 1
    You need an order by on the two queries. They are selecting top with no order by so there is no guarantee what rows will be returned. – Sean Lange Jul 31 '14 at 13:37
  • Yes. That query will not 'page' as you expect without an order by. You should add one, otherwise you'll end up paging to the same records. – Nick.Mc Jul 31 '14 at 23:48
0

How about:

select id,name from (
    select *   from (select top(3 + 6 - 1) * from mytable
    except
    select top(6 - 1) * from mytable) as newtable
    ) as table2 where condition
Zenilogix
  • 1,318
  • 1
  • 15
  • 31