How to get the 4th record and on from select query

14 Jul 2005 11:42 PM

TdarTdar

SELECT [itemVal] FROM #TempTB WHERE [ID] > (the fourth Record in the #TempTB)

Thanks

Tdar

14 Jul 2005 11:47 PM

Steve Kass

Tdar,

It depends what you mean by "fourth." Fourth in what order? Assuming
in order
of increasing ID, you could try these. If you mean something else,
you'll have to
make some changes.

select [itemVal]
from #TempTB
where [ID] > (
select top 1 [ID]
from (
    select top 4 [ID]
    from #TempTB
    order by [ID] asc
) F
order by [ID] desc
)

or

select [itemVal]
from #TempTB
where [ID] > ALL (
select top 4 [ID]
from #TempTB
order by [ID] asc
)

or

select [itemVal]
from #TempTB
where [ID] NOT IN (
select top 4 [ID]
from #TempTB
order by [ID] asc
)

Steve Kass
Drew University

TdarTdar wrote:

Show quote

>SELECT [itemVal] FROM #TempTB WHERE [ID] > (the fourth Record in the #TempTB)
>
>Thanks
>
>Tdar
>
>
>

14 Jul 2005 11:56 PM

TdarTdar

I will not have a clue what the part number(ID) would be, But i have a need
to grab from the 4th record and beyond skipping the first 3 records for the
output. Now I liked what you told me yesterday of:

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

Just that < 1022 is really to be the 4th record and beyond.

Hope that helps to explain it better.

Tdar

14 Jul 2005 11:59 PM

TdarTdar

Humm
select [itemVal]
from #TempTB
where [ID] NOT IN (
select top 4 [ID]
from #TempTB
order by [ID] asc
)

this should do it unless there is another way. Did not see that the first
time around.

Show quote

"Steve Kass" wrote:

> Tdar,
>
>   It depends what you mean by "fourth." Fourth in what order? Assuming
> in order
> of increasing ID, you could try these. If you mean something else,
> you'll have to
> make some changes.
>
> select [itemVal]
> from #TempTB
> where [ID] > (
>   select top 1 [ID]
>   from (
>     select top 4 [ID]
>     from #TempTB
>     order by [ID] asc
>   ) F
>   order by [ID] desc
> )
>
> or
>
> select [itemVal]
> from #TempTB
> where [ID] > ALL (
>   select top 4 [ID]
>   from #TempTB
>   order by [ID] asc
> )
>
> or
>
> select [itemVal]
> from #TempTB
> where [ID] NOT IN (
>   select top 4 [ID]
>   from #TempTB
>   order by [ID] asc
> )
>
> Steve Kass
> Drew University
>
> TdarTdar wrote:
>
> >SELECT [itemVal] FROM #TempTB WHERE [ID] > (the fourth Record in the #TempTB)
> >
> >Thanks
> >
> >Tdar
> >
> >
> >
>

15 Jul 2005 2:56 PM

Ross Presser

On Thu, 14 Jul 2005 19:47:04 -0400, Steve Kass wrote:

Show quote

> select [itemVal]
> from #TempTB
> where [ID] > ALL (
>   select top 4 [ID]
>   from #TempTB
>   order by [ID] asc
> )
>
> or
>
> select [itemVal]
> from #TempTB
> where [ID] NOT IN (
>   select top 4 [ID]
>   from #TempTB
>   order by [ID] asc
> )

Of these two, which is likely to result in a better query plan? > ALL or
NOT IN? Understandably if there are only 4 it probably doesn't matter, but
if it were 40000 then it might.

Would it be better to do something like this:

select [itemVal]
from #TempTB
where [ID] > (
   select max([ID])
   from (
      select top 40000 [ID]
      from #TempTB
      order by [ID] asc
   ) T1
)

15 Jul 2005 5:43 PM

Steve Kass

Ross,

The best way to answer a question like this is to run
some tests and look at estimated execution plans.

SK

Ross Presser wrote:

Show quote

>On Thu, 14 Jul 2005 19:47:04 -0400, Steve Kass wrote:
>
>
>
>>select [itemVal]
>>from #TempTB
>>where [ID] > ALL (
>> select top 4 [ID]
>> from #TempTB
>> order by [ID] asc
>>)
>>
>>or
>>
>>select [itemVal]
>>from #TempTB
>>where [ID] NOT IN (
>> select top 4 [ID]
>> from #TempTB
>> order by [ID] asc
>>)
>>
>>
>
>Of these two, which is likely to result in a better query plan? > ALL or
>NOT IN? Understandably if there are only 4 it probably doesn't matter, but
>if it were 40000 then it might.
>
>Would it be better to do something like this:
>
>select [itemVal]
>from #TempTB
>where [ID] > (
>   select max([ID])
>   from (
>      select top 40000 [ID]
>      from #TempTB
>      order by [ID] asc
>   ) T1
>)
>
>