SQL Stored Proc Possiably unique question

14 Jul 2005 3:47 AM

TdarTdar

Hello,
I have a Stored proc that will return top 9 records. I want to keep the same
output minus the first 4 records in another query Example:

Select top 9 * from [items]
1
2
3
4
5
6
7
8
9

I want the same results as the above but minus the same first four records.

Select Top 9 * from [items] (somehow miuns top 4 records for output of )
5
6
7
8
9

Now I know you can do something like this

select top 9 * from new1
1
2
3
4
5
6
7
8
9

select Top 6 * from New1 where id < 1022 order by ID desc
9
8
7
6
5
4

Now if there was a way to flip the results of
"select Top 6 * from New1 where id < 1022 order by ID desc"
another words re-ascend back to
4
5
6
7
8
9

before the output of the records to the program we would be in business.

Maybe there is a way with a temp table but not to familliar with them,
and franlky having a couple second preformance delay would not be good.

Any Ideas?

Tdar

14 Jul 2005 4:06 AM

Jonathan Chong

May be you can use temporary table to do it.

Create Table #TempTB(
[ID] [int] IDENTITY (1, 1) NOT NULL,
[itemVal] int
)

INSERT INTO #TempTB EXEC your_sp

SELECT [itemVal] FROM #TempTB WHERE [ID] > 4

DROP TABLE #TempTB

Show quote

"TdarTdar" <TdarT***@discussions.microsoft.com> wrote in message
news:252EDAB7-CC0E-4277-A06A-F9E722093D6C@microsoft.com...
> Hello,
> I have a Stored proc that will return top 9 records. I want to keep the
same
> output minus the first 4 records in another query Example:
>
> Select top 9 * from [items]
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
>
>
> I want the same results as the above but minus the same first four
records.
>
> Select Top 9 * from [items] (somehow miuns top 4 records for output of )
> 5
> 6
> 7
> 8
> 9
>
> Now I know you can do something like this
>
> select top 9 * from new1
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
>
> select Top 6 * from New1 where id < 1022 order by ID desc
> 9
> 8
> 7
> 6
> 5
> 4
>
> Now if there was a way to flip the results of
> "select Top 6 * from New1 where id < 1022 order by ID desc"
> another words re-ascend back to
> 4
> 5
> 6
> 7
> 8
> 9
>
> before the output of the records to the program we would be in business.
>
> Maybe there is a way with a temp table but not to familliar with them,
> and franlky having a couple second preformance delay would not be good.
>
> Any Ideas?
>
> Tdar
>
>

14 Jul 2005 4:43 AM

TdarTdar

Humm,
well if i can put the select statement where the your_sp is without calling
another
stored proc, then cool. But will have to check on the proformace, if there
is no other way then so be it.
Thanks Jonathan,
Tdar

Show quote

"Jonathan Chong" wrote:

> May be you can use temporary table to do it.
>
> Create Table #TempTB(
> [ID] [int] IDENTITY (1, 1) NOT NULL,
> [itemVal] int
> )
>
> INSERT INTO #TempTB EXEC your_sp
>
> SELECT [itemVal] FROM #TempTB WHERE [ID] > 4
>
> DROP TABLE #TempTB
>
>
> "TdarTdar" <TdarT***@discussions.microsoft.com> wrote in message
> news:252EDAB7-CC0E-4277-A06A-F9E722093D6C@microsoft.com...
> > Hello,
> > I have a Stored proc that will return top 9 records. I want to keep the
> same
> > output minus the first 4 records in another query Example:
> >
> > Select top 9 * from [items]
> > 1
> > 2
> > 3
> > 4
> > 5
> > 6
> > 7
> > 8
> > 9
> >
> >
> > I want the same results as the above but minus the same first four
> records.
> >
> > Select Top 9 * from [items] (somehow miuns top 4 records for output of )
> > 5
> > 6
> > 7
> > 8
> > 9
> >
> > Now I know you can do something like this
> >
> > select top 9 * from new1
> > 1
> > 2
> > 3
> > 4
> > 5
> > 6
> > 7
> > 8
> > 9
> >
> > select Top 6 * from New1 where id < 1022 order by ID desc
> > 9
> > 8
> > 7
> > 6
> > 5
> > 4
> >
> > Now if there was a way to flip the results of
> > "select Top 6 * from New1 where id < 1022 order by ID desc"
> > another words re-ascend back to
> > 4
> > 5
> > 6
> > 7
> > 8
> > 9
> >
> > before the output of the records to the program we would be in business.
> >
> > Maybe there is a way with a temp table but not to familliar with them,
> > and franlky having a couple second preformance delay would not be good.
> >
> > Any Ideas?
> >
> > Tdar
> >
> >
>
>
>

14 Jul 2005 4:57 AM

Uri Dimant

Hi
You don't need a temporary table at all

CREATE TABLE #Input
(
col1 INT NOT NULL Primary KEY,

)
INSERT INTO #Input VALUES (1)
INSERT INTO #Input VALUES (2)
INSERT INTO #Input VALUES (3)
INSERT INTO #Input VALUES (4)
INSERT INTO #Input VALUES (5)
INSERT INTO #Input VALUES (6)
INSERT INTO #Input VALUES (7)
INSERT INTO #Input VALUES (8)
INSERT INTO #Input VALUES (9)
INSERT INTO #Input VALUES (10)
INSERT INTO #Input VALUES (11)
INSERT INTO #Input VALUES (12)
GO
---one
SELECT col1 FROM
(
SELECT TOP 5 col1 FROM
(
SELECT TOP 9 col1 FROM #Input ORDER BY col1 ASC
)AS Der ORDER BY col1 DESC ) AS T
ORDER BY col1 ASC

---Second (Not really reliable)
SELECT col1 FROM #Input WHERE (SELECT COUNT(*)
FROM #Input I WHERE I.col1<=#Input.col1)
BETWEEN 5 AND 9

Show quote

"TdarTdar" <TdarT***@discussions.microsoft.com> wrote in message
news:252EDAB7-CC0E-4277-A06A-F9E722093D6C@microsoft.com...
> Hello,
> I have a Stored proc that will return top 9 records. I want to keep the
same
> output minus the first 4 records in another query Example:
>
> Select top 9 * from [items]
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
>
>
> I want the same results as the above but minus the same first four
records.
>
> Select Top 9 * from [items] (somehow miuns top 4 records for output of )
> 5
> 6
> 7
> 8
> 9
>
> Now I know you can do something like this
>
> select top 9 * from new1
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
>
> select Top 6 * from New1 where id < 1022 order by ID desc
> 9
> 8
> 7
> 6
> 5
> 4
>
> Now if there was a way to flip the results of
> "select Top 6 * from New1 where id < 1022 order by ID desc"
> another words re-ascend back to
> 4
> 5
> 6
> 7
> 8
> 9
>
> before the output of the records to the program we would be in business.
>
> Maybe there is a way with a temp table but not to familliar with them,
> and franlky having a couple second preformance delay would not be good.
>
> Any Ideas?
>
> Tdar
>
>

14 Jul 2005 5:04 AM

Steve Kass

How about

select * from (
select top 6 * from New1
where id < 1022
order by ID desc
) as S
order by ID asc

Confusingly, there are two very distinct meanings for
ORDER BY ID in SQL Server. When a TOP clause
is present, ORDER BY is part of the TOP .. ORDER BY
construction, which defines the rows under consideration.
When no TOP clause is present, ORDER BY determines
the order in which query results are presented to the client.
The first of these two meanings (with TOP) is not part of
the ANSI standard, so the confusion is not entirely
surprising.

You need both kinds of ORDER BY clauses in your query,
one to work with TOP 6 and identify specific rows, then
another to order the results.

I would prefer ways to specify these different meanings that
caused less confusion, so you could do something like this:

select top (ordered by ID desc) 6 *
from New1
order by ID asc

Maybe in a future version...

Steve Kass
Drew University

TdarTdar wrote:

Show quote

>Hello,
>I have a Stored proc that will return top 9 records. I want to keep the same
>output minus the first 4 records in another query Example:
>
>Select top 9 * from [items]
>1
>2
>3
>4
>5
>6
>7
>8
>9
>
>
>I want the same results as the above but minus the same first four records.
>
>Select Top 9 * from [items] (somehow miuns top 4 records for output of )
>5
>6
>7
>8
>9
>
>Now I know you can do something like this
>
>select top 9 * from new1
>1
>2
>3
>4
>5
>6
>7
>8
>9
>
>select Top 6 * from New1 where id < 1022 order by ID desc
>9
>8
>7
>6
>5
>4
>
>Now if there was a way to flip the results of
>"select Top 6 * from New1 where id < 1022 order by ID desc"
>another words re-ascend back to
>4
>5
>6
>7
>8
>9
>
>before the output of the records to the program we would be in business.
>
>Maybe there is a way with a temp table but not to familliar with them,
>and franlky having a couple second preformance delay would not be good.
>
>Any Ideas?
>
>Tdar
>
>
>
>