CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
arlen
arlen

Reputation: 1065

Sql Select top 2 , bottom 2 and 6 random records

How to select top 2 , bottom 2 and 6 random (not in Top 2 and Bottom 2) records of the table using one SQL select query?

Upvotes: 3

Views: 5224

Answers (3)

Alex Aza
Alex Aza

Reputation: 78487

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 sys.tables with your table name and alter order by name to specify order condition for top 2 and bottom 2.

Upvotes: 3

Bohemian
Bohemian

Reputation: 425238

Assuming the "order" is 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 tested query - it works

Upvotes: 1

Colin
Colin

Reputation: 2021

Perhaps not a single select statment, 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 along those lines. Basically the UNION All is the trick.

Upvotes: 1

Related Questions