Re: potential top

From: Philippe [MS] (ptrotin_at_online.microsoft.com)
Date: 10/19/04

Next message: Anith Sen: "Re: Toolbar in Design View of a table?"
Previous message: Philippe [MS]: "Re: SET NOCOUNT ON OPTION"
In reply to: CGW: "Re: potential top"
Messages sorted by: [ date ] [ thread ]

Date: Tue, 19 Oct 2004 21:32:15 +0200

If needed, I have wrote a store procedure allowing you to go from a specific
row number to another.

Example : EXEC tp_GetSqlRange 'SELECT * FROM Zip', 'ZIP_ID', 5, 10
=> get the row from 5 to 10 based on the ZIP_ID (for order by and
unicity.)

Hope this can help.

Phil.

"CGW" <CGW@discussions.microsoft.com> wrote in message
news:672D580A-70B6-464A-B3CA-5EFEF4081F1C@microsoft.com...
> Thanks for a creative solution. Though it doesn't exactly fit what we need
> here, it sure looks like something handy for later.
>
> Thank you.
>
> CGW
>
> "Adam Machanic" wrote:
>
> >
> > "CGW" <CGW@discussions.microsoft.com> wrote in message
> > news:A5842788-5254-4653-9EB3-889784F48D09@microsoft.com...
> > > When executing a top query, is there a way to return (in the same
query) a
> > > rowcount for what the query would return without the top?
> > >
> > > For example
> > > SELECT TOP 100 FROM MyTable WHERE MyCondition
> > > Can I add something to that query to return what the COUNT (*) would
be
> > > without the "top 100" at the same time as the top 100 records. A
second
> > > query, in this environment, is costly.
> >
> > Depending on how much data you're talking about, it might actually
be
> > cheaper to do the entire select into a temp table and then select TOP
100
> > and the COUNT(*) from that:
> >
> > SELECT x, y, z
> > INTO #SomeTable
> > FROM MyTable
> > WHERE MyCondition
> >
> > SELECT TOP 100 x, y, z
> > FROM #SomeTable
> > ORDER BY x
> >
> > SELECT COUNT(*)
> > FROM #SomeTable
> >
> > Alternatively, if you're dealing with big LOB columns (which would
be
> > expensive to move into the temp table), you could just select the
primary
> > keys into the temp table and then pull back the data from the main table
> > that way:
> >
> > SELECT PK, x
> > INTO #SomeTable
> > FROM MyTable
> > WHERE MyCondition
> >
> > SELECT x, y, z
> > FROM MyTable
> > WHERE PK IN
> > (SELECT TOP 100 PK
> > FROM #SomeTable
> > ORDER BY x)
> >
> > SELECT COUNT(*)
> > FROM #SomeTable
> >
> >
> >
> >

begin 666 tp_GetSqlRange.sql
M__YI`&8`( !E`'@`:0!S`'0`<P`@`"@`<P!E`&P`90!C`'0`( `J`" `9@!R
M`&\`;0`@`&0`8@!O`"X`<P!Y`',`;P!B`&H`90!C`'0`<P`@`'<`: !E`'(`
M90`@`&D`9 `@`#T`( !O`&(`:@!E`&,`= !?`&D`9 `H`$X`)P!;`&0`8@!O
M`%T`+@!;`'0`< !?`$<`90!T`%,`<0!L`%(`80!N`&<`90!=`"<`*0`@`&$`
M;@!D`" `3P!"`$H`10!#`%0`4 !2`$\`4 !%`%(`5 !9`"@`:0!D`"P`( !.
M`"<`20!S`% `<@!O`&,`90!D`'4`<@!E`"<`*0`@`#T`( `Q`"D`#0`*`&0`
M<@!O`' `( !P`'(`;P!C`&4`9 !U`'(`90`@`%L`9 !B`&\`70`N`%L`= !P
M`%\`1P!E`'0`4P!Q`&P`4@!A`&X`9P!E`%T`#0`*`$<`3P`-``H`#0`*`%,`
M10!4`" `40!5`$\`5 !%`$0`7P!)`$0`10!.`%0`20!&`$D`10!2`" `3P!.
M`" `#0`*`$<`3P`-``H`4P!%`%0`( !!`$X`4P!)`%\`3@!5`$P`3 !3`" `
M3P!&`$8`( `-``H`1P!/``T`"@`-``H`#0`*``T`"@`O`"H`+0`M`"T`+0`M
M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`
M+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M
M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`
M+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M
M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`
M+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M
M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`
M+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M
M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`
M+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M
M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`
M+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M
M`"T`+0`M`"T`+0`M``T`"@`\`%,`= !O`'(`90!D`% `<@!O`&,`90!D`'4`
M<@!E`" `;@!A`&T`90`]`"(`= !P`%\`1P!E`'0`4P!Q`&P`4@!A`&X`9P!E
M`"(`/@`-``H`#0`*``D`/ !/`&(`:@!E`&,`= `^``D`00!L`&P`;P!W`" `
M>0!O`'4`( !T`&\`( !G`&4`= `@`&0`80!T`&$`( !F`'(`;P!M`" `80`@
M`',`< !E`&,`:0!F`&D`8P`@`'(`80!N`&<`90`@`&X`=0!M`&(`90!R`" `
M= !O`" `80`@`',`< !E`&,`:0!F`&D`8P`@`'(`80!N`&<`90`@`&X`=0!M
M`&(`90!R``D`"0`)``D`"0`)``D`"0`\`"\`3P!B`&H`90!C`'0`/@`@``T`
M"@`-``H`"0`\`$@`:0!S`'0`;P!R`'D`( `@`$$`=0!T`&@`;P!R`" `( `@
M`" `( `]`" `(@!0`&@`:0!L`&D`< !P`&4`( !4`%(`3P!4`$D`3@`@`"T`
M( !P`'0`<@!O`'0`:0!N`$ `;0!I`&,`<@!O`',`;P!F`'0`+@!C`&\`;0`B
M`" `( !$`&$`= !E`" `/0`@`"(`, `V`"\`, `R`"\`,@`P`# `- `B`#X`
M( !#`&\`<@!E`" `,@`N`# `( `)``D`"0`)``D`"0`)``D`/ `O`$@`:0!S
M`'0`;P!R`'D`/@`-``H`#0`*`#P`+P!3`'0`;P!R`&4`9 !0`'(`;P!C`&4`
M9 !U`'(`90`^``T`"@`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M
M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`
M+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M
M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`
M+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M
M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`
M+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M
M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`
M+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M
M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`
M+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M
M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`
M+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`+0`M`"T`*@`O``T`"@!#
M`%(`10!!`%0`10`@`" `4 !2`$\`0P!%`$0`50!2`$4`( !T`' `7P!'`&4`
M= !3`'$`; !2`&$`;@!G`&4`#0`*`"@`#0`*``D`0 !3`%$`3 !?`%0`80!B
M`&P`90`)``D`;@!V`&$`<@!C`&@`80!R`"@`- `P`# `, `I`"P`"0`)`"T`
M+0`@`$4`> !E`&T`< !L`&4`( `Z`" `"0!3`$4`3 !%`$,`5 `@`"H`( !&
M`%(`3P!-`" `6@!I`' `#0`*``D`0 !4`&$`8@!L`&4`2P!E`'D`"0`)`&X`
M=@!A`'(`8P!H`&$`<@`H`#0`, `P`"D`+ `)``D`+0`M`" `"0`)`%H`20!0
M`%\`20!$``T`"@`)`$ `4@!A`&X`9P!E`$T`:0!N``D`"0!I`&X`= `L``D`
M"0`)`"T`+0`@``D`"0`U``T`"@`)`$ `4@!A`&X`9P!E`$T`80!X``D`"0!I
M`&X`= `L``D`"0`)`"T`+0`@``D`"0`Q`# `#0`*``D`0 !'`&4`= !4`&\`
M= !A`&P`0P!O`'4`;@!T``D`:0!N`'0`( `]`" `,0`-``H`*0`-``H`00!3
M``T`"@!"`$4`1P!)`$X`#0`*``D`4P!%`%0`( !.`$\`0P!/`%4`3@!4`" `
M3P!.``T`"@`-``H`"0!$`$4`0P!,`$$`4@!%`" `0 !3`%$`3 !3`'0`<@!I
M`&X`9P`)``D`;@!V`&$`<@!C`&@`80!R`"@`- `P`# `, `I``T`"@`)`$0`
M10!#`$P`00!2`$4`( ! `$,`;P!U`&X`= `)``D`:0!N`'0`#0`*``D`1 !%
M`$,`3 !!`%(`10`@`$ `4 !A`'(`;0!$`&4`9@!I`&X`:0!T`&D`;P!N`" `
M"0!N`'8`80!R`&,`: !A`'(`* `U`# `, `I``T`"@`)`$0`10!#`$P`00!2
M`$4`( ! `$T`80!X`%0`;P!T`&$`; !#`&\`=0!N`'0`"0!I`&X`= `-``H`
M"0!$`$4`0P!,`$$`4@!%`" `0 !2`&$`;@!G`&4`20!N`'8`90!R`',`:0!O
M`&X`"0!I`&X`= `-``H`"0!$`$4`0P!,`$$`4@!%`" `0 !.`'4`;0!B`&4`
M<@!/`&8`4@!O`'<`<P`)`&D`;@!T``T`"@`)``T`"@`)`$0`10!#`$P`00!2
M`$4`( ! `$D`1 !?`$T`20!.``D`"0!N`'8`80!R`&,`: !A`'(`* `T`# `
M, `I``T`"@`-``H`"0`M`"T`( !%`&X`<P!U`'(`90`@`'0`: !A`'0`( !
M`%(`80!N`&<`90!-`&D`;@`@`&D`<P`@`&8`90!W`&4`<@`@`'0`: !A`&X`
M( ! `%(`80!N`&<`90!-`&$`> `-``H`"0!)`$8`( ! `%(`80!N`&<`90!-
M`&D`;@`@`#X`/0`@`$ `4@!A`&X`9P!E`$T`80!X``T`"@`)`$(`10!'`$D`
M3@`-``H`"0`)`%,`10!4`" `0 !2`&$`;@!G`&4`20!N`'8`90!R`',`:0!O
M`&X`( `]`" `0 !2`&$`;@!G`&4`30!I`&X`#0`*``D`"0!3`$4`5 `@`$ `
M4@!A`&X`9P!E`$T`:0!N`" `/0`@`$ `4@!A`&X`9P!E`$T`80!X``T`"@`)
M``D`4P!%`%0`( ! `%(`80!N`&<`90!-`&$`> `@`#T`( ! `%(`80!N`&<`
M90!)`&X`=@!E`'(`<P!I`&\`;@`-``H`"0!%`$X`1 `-``H`#0`*``D`4P!%
M`%0`( ! `$X`=0!M`&(`90!R`$\`9@!2`&\`=P!S`" `/0`@`$ `4@!A`&X`
M9P!E`$T`80!X`" `+0`@`$ `4@!A`&X`9P!E`$T`:0!N`" `*P`@`#$`#0`*
M``T`"@`)`"T`+0`@`$<`90!T`" `= !O`'0`80!L`" `;@!U`&T`8@!E`'(`
M( !O`&8`( !R`&\`=P!S`" `* !N`&\`= `@`'(`80!N`&<`90`@`&0`90!P
M`&4`;@!D`&$`;@!T`"D`#0`*``D`20!&`" `0 !'`&4`= !4`&\`= !A`&P`
M0P!O`'4`;@!T`" `/0`@`#$`#0`*``D`0@!%`$<`20!.``T`"@`)``D`4P!%
M`%0`( ! `%,`40!,`%,`= !R`&D`;@!G`" `/0`@`"<`4P!%`$P`10!#`%0`
M( ! `$T`80!X`%0`;P!T`&$`; !#`&\`=0!N`'0`( `]`" `8P!O`'4`;@!T
M`"@`*@`I`" `1@!2`$\`30`@`"@`)P`@`"L`( ! `%,`40!,`%\`5 !A`&(`
M; !E`" `*P`@`"<`*0`@`'(`90!Q`"<`#0`*``D`"0!3`$4`5 `@`$ `4 !A
M`'(`;0!$`&4`9@!I`&X`:0!T`&D`;P!N`" `/0`@`"<`0 !-`&$`> !4`&\`
M= !A`&P`0P!O`'4`;@!T`" `:0!N`'0`( !/`%4`5 !0`%4`5 `G``T`"@`)
M``D`10!8`$4`0P!5`%0`10`@`',`< !?`&4`> !E`&,`=0!T`&4`<P!Q`&P`
M( ! `%,`40!,`%,`= !R`&D`;@!G`"P`( ! `% `80!R`&T`1 !E`&8`:0!N
M`&D`= !I`&\`;@`L`" `0 !-`&$`> !4`&\`= !A`&P`0P!O`'4`;@!T`" `
M3P!5`%0`4 !5`%0`#0`*``D`10!.`$0`#0`*``T`"@`)`"T`+0`@`$<`90!T
M`" `<@!A`&X`9P!E`',`#0`*``D`4P!%`%0`( ! `%,`40!,`%,`= !R`&D`
M;@!G`" `/0`@`"<`4P!%`$P`10!#`%0`( ! `$D`1 !?`$T`20!.`" `/0`@
M`$D`4P!.`%4`3 !,`"@`>@`N`"<`( `K`" `0 !4`&$`8@!L`&4`2P!E`'D`
M( `K`" `)P`L`" `)P`G`"<`)P`I`" `1@!2`$\`30`@`"@`4P!%`$P`10!#
M`%0`( !4`$\`4 `@`"<`( `K`" `0P!/`$X`5@!%`%(`5 `H`&X`=@!A`'(`
M8P!H`&$`<@`L`" `0 !2`&$`;@!G`&4`30!I`&X`*0`@`"L`( `G`" `<@!E
M`'$`+@`G`" `*P`@`$ `5 !A`&(`; !E`$L`90!Y`" `*P`@`"<`( !&`%(`
M3P!-`" `* `G`" `*P`@`$ `4P!1`$P`7P!4`&$`8@!L`&4`( `K`" `)P`I
M`" `<@!E`'$`( !/`%(`1 !%`%(`( !"`%D`( !R`&4`<0`N`"<`( `K`" `
M0 !4`&$`8@!L`&4`2P!E`'D`( `K`" `)P`I`" `>@`G``T`"@`)`%,`10!4
M`" `0 !0`&$`<@!M`$0`90!F`&D`;@!I`'0`:0!O`&X`( `]`" `)P! `$D`
M1 !?`$T`20!.`" `;@!V`&$`<@!C`&@`80!R`"@`,@`P`# `*0`@`$\`50!4
M`% `50!4`"<`#0`*``D`10!8`$4`0P!5`%0`10`@`',`< !?`&4`> !E`&,`
M=0!T`&4`<P!Q`&P`( ! `%,`40!,`%,`= !R`&D`;@!G`"P`( ! `% `80!R
M`&T`1 !E`&8`:0!N`&D`= !I`&\`;@`L`" `0 !)`$0`7P!-`$D`3@`@`$\`
M50!4`% `50!4``T`"@`-``H`"0`M`"T`( !'`&4`= `@`&0`80!T`&$`#0`*
M``D`20!&`" `0 !)`$0`7P!-`$D`3@`@`#T`( `G`"<`#0`*``D`0@!%`$<`
M20!.``T`"@`)``D`4P!%`%0`( ! `%,`40!,`%,`= !R`&D`;@!G`" `/0`@
M`"<`4P!%`$P`10!#`%0`( !4`$\`4 `@`"<`( `K`" `0P!/`$X`5@!%`%(`
M5 `H`&X`=@!A`'(`8P!H`&$`<@`L`" `0 !.`'4`;0!B`&4`<@!/`&8`4@!O
M`'<`<P`I`" `*P`@`"<`( `J`" `1@!2`$\`30`@`"@`)P`@`"L`( ! `%,`
M40!,`%\`5 !A`&(`; !E`" `*P`@`"<`*0`@`'(`90!Q`" `3P!2`$0`10!2
M`" `0@!9`" `<@!E`'$`+@`G`" `*P`@`$ `5 !A`&(`; !E`$L`90!Y``T`
M"@`)`$4`3@!$``T`"@`)`$4`3 !3`$4`#0`*``D`0@!%`$<`20!.``D`#0`*
M``D`"0!3`$4`5 `@`$ `4P!1`$P`4P!T`'(`:0!N`&<`( `]`" `)P!3`$4`
M3 !%`$,`5 `@`%0`3P!0`" `)P`@`"L`( !#`$\`3@!6`$4`4@!4`"@`;@!V
M`&$`<@!C`&@`80!R`"P`( ! `$X`=0!M`&(`90!R`$\`9@!2`&\`=P!S`"D`
M( `K`" `)P`@`"H`( !&`%(`3P!-`" `* `G`" `*P`@`$ `4P!1`$P`7P!4
M`&$`8@!L`&4`( `K`" `)P`I`" `<@!E`'$`( !7`$@`10!2`$4`( !R`&4`
M<0`N`"<`( `K`" `0 !4`&$`8@!L`&4`2P!E`'D`( `K`" `)P`@`#X`/0`@
M`$ `20!$`%\`30!)`$X`( !/`%(`1 !%`%(`( !"`%D`( !R`&4`<0`N`"<`
M( `K`" `0 !4`&$`8@!L`&4`2P!E`'D`#0`*``D`10!.`$0`#0`*``T`"@`)
M`%,`10!4`" `0 !0`&$`<@!M`$0`90!F`&D`;@!I`'0`:0!O`&X`( `]`" `
M)P! `$D`1 !?`$T`20!.`" `;@!V`&$`<@!C`&@`80!R`"@`,@`P`# `*0`G
M``T`"@`)`$4`6 !%`$,`50!4`$4`( !S`' `7P!E`'@`90!C`'4`= !E`',`
M<0!L`" `0 !3`%$`3 !3`'0`<@!I`&X`9P`L`" `0 !0`&$`<@!M`$0`90!F
M`&D`;@!I`'0`:0!O`&X`+ `@`$ `20!$`%\`30!)`$X`#0`*``T`"@`)`"T`
M+0`@`% `<@!I`&X`= `@`'0`: !E`" `= !O`'0`80!L`" `;@!U`&T`8@!E
M`'(`( !O`&8`( !R`&\`=P!S``T`"@`)`$D`1@`@`$ `1P!E`'0`5 !O`'0`
M80!L`$,`;P!U`&X`= `@`#T`( `Q``T`"@`)`$(`10!'`$D`3@`-``H`"0`)
M`%,`10!,`$4`0P!4`" `0 !-`&$`> !4`&\`= !A`&P`0P!O`'4`;@!T`" `
M00!3`" `5 !O`'0`80!L`$X`=0!M`&(`90!R`$\`9@!2`&\`=P!S``T`"@`)
M`$4`3@!$``T`"@!%`$X`1 `-``H`#0`*`$<`3P`-``H`4P!%`%0`( !1`%4`
M3P!4`$4`1 !?`$D`1 !%`$X`5 !)`$8`20!%`%(`( !/`$8`1@`@``T`"@!'
M`$\`#0`*`%,`10!4`" `00!.`%,`20!?`$X`50!,`$P`4P`@`$\`3@`@``T`
."@!'`$\`#0`*``T`"@``
`
end

Next message: Anith Sen: "Re: Toolbar in Design View of a table?"
Previous message: Philippe [MS]: "Re: SET NOCOUNT ON OPTION"
In reply to: CGW: "Re: potential top"
Messages sorted by: [ date ] [ thread ]

Relevant Pages

Re: potential top
... CGW ... >> query, in this environment, is costly. ... > cheaper to do the entire select into a temp table and then select TOP 100 ... > WHERE MyCondition ...
(microsoft.public.sqlserver.programming)
Re: Indexs
... >so what is the syntax. ... Hi Phil, ... CREATE TABLE #temp (PK int NOT NULL, ... (Remove _NO_ and _SPAM_ to get my e-mail address) ...
(microsoft.public.sqlserver.mseq)
21 today
... Phil ... Guildford 03/05/06 4:58Z ... Temp 7.9°C ... Rain 0.0mm ...
(uk.sci.weather)
Re: Model thickness dams
... for a -5c temp as a better guide? ... Phil ... Prev by Date: ...
(uk.sci.weather)
Re: After SP2 upgrade
... Phil --> It takes around 2 GB of Hard drive, so it is stranger tht yr HDD ... >> install fail to remove some TEMP. ...
(microsoft.public.windowsxp.general)