Geek asks and answers

Sql Select top 2, bottom 2 and 6 random records

How to select top 2, bottom 2 and 6 random (not in Upper 2 and Lower 2) table entries using a single SQL select query?

+3
source share
3 answers

In MS SQL 2005/2008:

with cte
as
(
    select 
        row_number() over (order by name) RowNumber, 
        row_number() over (order by newid()) RandomOrder,
        count(*) over() Total,
        *
    from sys.tables
)
select *
from cte
where RowNumber <= 2 or Total - RowNumber + 1 <= 2
union all
select *
from
(
    select top 6 *
    from cte 
    where RowNumber > 2 and Total - RowNumber > 2
    order by RandomOrder
) tt

Replace the sys.tablestables with your name and change order by nameto indicate the order condition for the top 2 and bottom 2.

+3
source

There may not be one selected item, but it can be executed in one call:

/* Top 2 - change order by to get the 'proper' top 2 */
SELECT * from table ORDER BY id DESC LIMIT 2
UNION ALL
/* Random 6.. You may want to add a WHERE and random data to get the random 6 */
/* Old Statement before edit - SELECT * from table LIMIT 6 */

SELECT * from table t
  LEFT JOIN (SELECT * from table ORDER BY id DESC LIMIT 2) AS top ON top.id = t.id
  LEFT JOIN (SELECT * from table ORDER BY id DESC LIMIT 2) AS bottom ON bottom.id = t.id
WHERE ISNULL(top.id ) AND ISNULL(bottom.id)
ORDER BY RANDOM()
LIMIT 6

UNION ALL
/* Bottom 2 - change order by to get the 'proper' bottom 2 */
SELECT * from table ORDER BY id ASC LIMIT 2

Something like that. In principle UNION All, this is a trick.

+1
source

Assuming the "order" is indicated by the id column:

select * from (select id, id from my_table order by id limit 2) t1
union
select * from (select id, id from my_table where id not in (
    select * from (select id from my_table order by id asc limit 2) t22
    union
    select * from (select id from my_table order by id desc limit 2 ) t23)
order by rand()
limit 6) t2
union
select * from (select id, id from my_table order by id desc limit 2) t3

EDIT: Fixed syntax and verified request - it works

+1
source

More articles:


All Articles