|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
select in rangeHi
I want to write a SP that has 2 params: @begin, @end. This SP returns records from the order "@begin" to "@end". For example: set @begin = 5 set @end = 10 I want to take records from 5 to 10. Thank for any reply GhostNguyen Try this instead of sp
SELECT <COLUMNS > FROM <TABLE> WHERE <COLUMN> BETWEEN 5 AND 10 Regards R.D Show quote "ghostnguyen" wrote: > Hi > > I want to write a SP that has 2 params: @begin, @end. > This SP returns records from the order "@begin" to "@end". > For example: > set @begin = 5 > set @end = 10 > I want to take records from 5 to 10. > > Thank for any reply > > GhostNguyen > > > > > > > Assuming that you have a column within your table to rank the rows so you
can tell which one is 5, 6, 7, etc... Then if you want the rows inclusive of the end points you could try WHERE YourColumn BETWEEN @begin and @end or if you want the rows exclusive of the end points you could use WHERE YourColumn > @begin and YourColumn < @end -- Show quote--Brian (Please reply to the newsgroups only.) "ghostnguyen" <ghostngu***@yahoo.com> wrote in message news:%230hSSmquFHA.3596@TK2MSFTNGP15.phx.gbl... > Hi > > I want to write a SP that has 2 params: @begin, @end. > This SP returns records from the order "@begin" to "@end". > For example: > set @begin = 5 > set @end = 10 > I want to take records from 5 to 10. > > Thank for any reply > > GhostNguyen > > > > > > Hi
The problem is my column is id column. It has integer value and automatically increase. Therefore, it is not continue, for example: 1,3,5,6,7... So I cant use BETWEEN. GhostNguyen Show quote "Brian Lawton" <brian.k.law***@redtailcreek.com> wrote in message news:uCX$rqquFHA.2948@TK2MSFTNGP15.phx.gbl... > Assuming that you have a column within your table to rank the rows so you > can tell which one is 5, 6, 7, etc... Then if you want the rows inclusive > of the end points you could try > > WHERE YourColumn BETWEEN @begin and @end > > or if you want the rows exclusive of the end points you could use > > WHERE YourColumn > @begin and YourColumn < @end > > -- > --Brian > (Please reply to the newsgroups only.) > > > "ghostnguyen" <ghostngu***@yahoo.com> wrote in message > news:%230hSSmquFHA.3596@TK2MSFTNGP15.phx.gbl... >> Hi >> >> I want to write a SP that has 2 params: @begin, @end. >> This SP returns records from the order "@begin" to "@end". >> For example: >> set @begin = 5 >> set @end = 10 >> I want to take records from 5 to 10. >> >> Thank for any reply >> >> GhostNguyen >> >> >> >> >> >> > > I'm not sure I understand your question then... From what you described,
BETWEEN would return the row with where the column equals 5, 6, 7, ..., 10. Could you provide a quick example of rows and show which ones you want returned? -- Show quote--Brian (Please reply to the newsgroups only.) "ghostnguyen" <ghostngu***@yahoo.com> wrote in message news:uJiqEwquFHA.2008@TK2MSFTNGP10.phx.gbl... > Hi > > The problem is my column is id column. It has integer value and > automatically increase. Therefore, it is not continue, for example: > 1,3,5,6,7... So I cant use BETWEEN. > > GhostNguyen > > > "Brian Lawton" <brian.k.law***@redtailcreek.com> wrote in message > news:uCX$rqquFHA.2948@TK2MSFTNGP15.phx.gbl... >> Assuming that you have a column within your table to rank the rows so you >> can tell which one is 5, 6, 7, etc... Then if you want the rows >> inclusive of the end points you could try >> >> WHERE YourColumn BETWEEN @begin and @end >> >> or if you want the rows exclusive of the end points you could use >> >> WHERE YourColumn > @begin and YourColumn < @end >> >> -- >> --Brian >> (Please reply to the newsgroups only.) >> >> >> "ghostnguyen" <ghostngu***@yahoo.com> wrote in message >> news:%230hSSmquFHA.3596@TK2MSFTNGP15.phx.gbl... >>> Hi >>> >>> I want to write a SP that has 2 params: @begin, @end. >>> This SP returns records from the order "@begin" to "@end". >>> For example: >>> set @begin = 5 >>> set @end = 10 >>> I want to take records from 5 to 10. >>> >>> Thank for any reply >>> >>> GhostNguyen >>> >>> >>> >>> >>> >>> >> >> > > For example, the table has two column: ID(primary key) and Name.
ID Name 1 AA 2 AB 5 BC 6 DB 7 DF 9 FE 11 ET 12 TD 14 DO 15 SP Note: the value of ID is unique, increasing but not continuous. Case 1: Let saying @begin = 2, @end = 6 The result is: 2 AB 5 BC 6 DB 7 DF 9 FE 11 ET Case 2: Let saying @begin = 3, @end = 5 The result is: 5 BC 6 DB 7 DF Show quote "Brian Lawton" <brian.k.law***@redtailcreek.com> wrote in message news:%230cO0zquFHA.2212@TK2MSFTNGP15.phx.gbl... > I'm not sure I understand your question then... From what you described, > BETWEEN would return the row with where the column equals 5, 6, 7, ..., > 10. Could you provide a quick example of rows and show which ones you want > returned? > > -- > --Brian > (Please reply to the newsgroups only.) > > > "ghostnguyen" <ghostngu***@yahoo.com> wrote in message > news:uJiqEwquFHA.2008@TK2MSFTNGP10.phx.gbl... >> Hi >> >> The problem is my column is id column. It has integer value and >> automatically increase. Therefore, it is not continue, for example: >> 1,3,5,6,7... So I cant use BETWEEN. >> >> GhostNguyen >> >> >> "Brian Lawton" <brian.k.law***@redtailcreek.com> wrote in message >> news:uCX$rqquFHA.2948@TK2MSFTNGP15.phx.gbl... >>> Assuming that you have a column within your table to rank the rows so >>> you can tell which one is 5, 6, 7, etc... Then if you want the rows >>> inclusive of the end points you could try >>> >>> WHERE YourColumn BETWEEN @begin and @end >>> >>> or if you want the rows exclusive of the end points you could use >>> >>> WHERE YourColumn > @begin and YourColumn < @end >>> >>> -- >>> --Brian >>> (Please reply to the newsgroups only.) >>> >>> >>> "ghostnguyen" <ghostngu***@yahoo.com> wrote in message >>> news:%230hSSmquFHA.3596@TK2MSFTNGP15.phx.gbl... >>>> Hi >>>> >>>> I want to write a SP that has 2 params: @begin, @end. >>>> This SP returns records from the order "@begin" to "@end". >>>> For example: >>>> set @begin = 5 >>>> set @end = 10 >>>> I want to take records from 5 to 10. >>>> >>>> Thank for any reply >>>> >>>> GhostNguyen >>>> >>>> >>>> >>>> >>>> >>>> >>> >>> >> >> > > ghoston
Dont you find contradiction in case 1 to 2. In case one you start from second row and take all rows till 2+6 =8 in the case2 you started from 3 took till five be clear what exactly you want. POST DDL/sample data Regards R.D Show quote "ghostnguyen" wrote: > For example, the table has two column: ID(primary key) and Name. > ID Name > 1 AA > 2 AB > 5 BC > 6 DB > 7 DF > 9 FE > 11 ET > 12 TD > 14 DO > 15 SP > > Note: the value of ID is unique, increasing but not continuous. > > Case 1: Let saying @begin = 2, @end = 6 > The result is: > 2 AB > 5 BC > 6 DB > 7 DF > 9 FE > 11 ET > > Case 2: Let saying @begin = 3, @end = 5 > The result is: > 5 BC > 6 DB > 7 DF > > > > > > > > "Brian Lawton" <brian.k.law***@redtailcreek.com> wrote in message > news:%230cO0zquFHA.2212@TK2MSFTNGP15.phx.gbl... > > I'm not sure I understand your question then... From what you described, > > BETWEEN would return the row with where the column equals 5, 6, 7, ..., > > 10. Could you provide a quick example of rows and show which ones you want > > returned? > > > > -- > > --Brian > > (Please reply to the newsgroups only.) > > > > > > "ghostnguyen" <ghostngu***@yahoo.com> wrote in message > > news:uJiqEwquFHA.2008@TK2MSFTNGP10.phx.gbl... > >> Hi > >> > >> The problem is my column is id column. It has integer value and > >> automatically increase. Therefore, it is not continue, for example: > >> 1,3,5,6,7... So I cant use BETWEEN. > >> > >> GhostNguyen > >> > >> > >> "Brian Lawton" <brian.k.law***@redtailcreek.com> wrote in message > >> news:uCX$rqquFHA.2948@TK2MSFTNGP15.phx.gbl... > >>> Assuming that you have a column within your table to rank the rows so > >>> you can tell which one is 5, 6, 7, etc... Then if you want the rows > >>> inclusive of the end points you could try > >>> > >>> WHERE YourColumn BETWEEN @begin and @end > >>> > >>> or if you want the rows exclusive of the end points you could use > >>> > >>> WHERE YourColumn > @begin and YourColumn < @end > >>> > >>> -- > >>> --Brian > >>> (Please reply to the newsgroups only.) > >>> > >>> > >>> "ghostnguyen" <ghostngu***@yahoo.com> wrote in message > >>> news:%230hSSmquFHA.3596@TK2MSFTNGP15.phx.gbl... > >>>> Hi > >>>> > >>>> I want to write a SP that has 2 params: @begin, @end. > >>>> This SP returns records from the order "@begin" to "@end". > >>>> For example: > >>>> set @begin = 5 > >>>> set @end = 10 > >>>> I want to take records from 5 to 10. > >>>> > >>>> Thank for any reply > >>>> > >>>> GhostNguyen > >>>> > >>>> > >>>> > >>>> > >>>> > >>>> > >>> > >>> > >> > >> > > > > > > > On Fri, 16 Sep 2005 17:47:13 +0700, ghostnguyen wrote:
>Hi Hi GhostNguyen,> >The problem is my column is id column. It has integer value and >automatically increase. Therefore, it is not continue, for example: >1,3,5,6,7... So I cant use BETWEEN. > >GhostNguyen Check out this page: http://www.aspfaq.com/show.asp?id=2120. The first half of the page shows techniques to do this in ASP; the second half has several techniques to do this in T-SQL. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics