Prev: from a stored procedure: how to return datasets and their tabl
Next: Query Suggestions?
From: nLL on 22 Jul 2008 07:04
with below query, i select top 5 top and bottom records from the table
and then select a random record within selected top 10.

---
set @selectedId =
(
select top 1 id from
(

select TOP 10 * from
(
SELECT
ROW_NUMBER() OVER(order by earned desc, spent asc, showed asc) AS
High,
ROW_NUMBER() OVER(order by earned asc, spent desc, showed desc) AS
Low,
id, linkId, earned, spent, (earned-spent) as credits, showed
FROM bannerExchange where (earned-spent >0) and (not
id=(a)bannerExchangeId ) and (toplistId=(a)toplistId)
) ttt
where Low <= 5 or High <= 5 order by High asc

) t order by newid()
)

---

my question is. is it possible to add 5 more to the selection from the
middle of table?
From: Steve Kass on 22 Jul 2008 08:11
Yes, you can do this. Add

OR Low - High BETWEEN -2 AND 2

This will give you either 4 or 5 rows from the middle,
depending on whether the number of rows meeting the
inner WHERE condition is even or odd.

Steve Kass
Drew University
http://www.stevekass.com

nLL wrote:

> with below query, i select top 5 top and bottom records from the table
> and then select a random record within selected top 10.
>
> ---
> set @selectedId =
> (
> select top 1 id from
> (
>
> select TOP 10 * from
> (
> SELECT
> ROW_NUMBER() OVER(order by earned desc, spent
> asc, showed asc) AS High,
> ROW_NUMBER() OVER(order by earned asc, spent
> desc, showed desc) AS Low,
> id, linkId, earned, spent, (earned-spent) as
> credits, showed
> FROM bannerExchange where (earned-spent >0)
> and (not id=(a)bannerExchangeId ) and (toplistId=(a)toplistId)
> ) ttt
> where Low <= 5 or High <= 5 order by High asc
>
> ) t order by newid()
> )
>
> ---
>
> my question is. is it possible to add 5 more to the selection from the
> middle of table?
From: nLL on 22 Jul 2008 08:58
Steve Kass wrote:
> Yes, you can do this. Add
>
> OR Low - High BETWEEN -2 AND 2
>
> This will give you either 4 or 5 rows from the middle,
> depending on whether the number of rows meeting the
> inner WHERE condition is even or odd.
>
> Steve Kass
> Drew University
> http://www.stevekass.com
>
> nLL wrote:
>
>> with below query, i select top 5 top and bottom records from the table
>> and then select a random record within selected top 10.
>>
>> ---
>> set @selectedId =
>> (
>> select top 1 id from
>> (
>>
>> select TOP 10 * from
>> (
>> SELECT
>> ROW_NUMBER() OVER(order by earned desc, spent
>> asc, showed asc) AS High,
>> ROW_NUMBER() OVER(order by earned asc, spent
>> desc, showed desc) AS Low,
>> id, linkId, earned, spent, (earned-spent) as
>> credits, showed
>> FROM bannerExchange where (earned-spent >0)
>> and (not id=(a)bannerExchangeId ) and (toplistId=(a)toplistId)
>> ) ttt
>> where Low <= 5 or High <= 5 order by High asc
>>
>> ) t order by newid()
>> )
>> ---
>>
>> my question is. is it possible to add 5 more to the selection from the
>> middle of table?

thanks
 | 
Pages: 1
Prev: from a stored procedure: how to return datasets and their tabl
Next: Query Suggestions?