Select Any Random Records from the table

ONE OF THE COMMON INTERVIEW QUESTION

SELECT ANY RANDOM RECORDS FROM THE TABLE [LETS SAY TWO RECORDS] AND ORDER BY [SAPID OR DEP OR NAME OR GENDER]

select * from employee

HCLDEP      SAPID             NAME              GENDER            project

1           514               ANURAG            MALE              NULL

2           325               SIBA              MALE              NULL

3           888               MUKUA             MALE              NULL

4           888               RITA              FEMALE            NULL

3           243               BIKASH            MALE              NULL

3           711               AKASH             MALE              NULL

2           500               RUCHI             FEMALE            NULL

2           750               PRACHI            FEMALE            NULL

2           620               PUSHPA            FEMALE            NULL

SO TOTAL 9 RECORDS

NEWID()

SELECT TOP 1 * FROM employee ORDER BY NEWID()

HCLDEP      SAPID      NAME        GENDER      project

3            243        BIKASH      MALE       NULL

--NOW THIS WILL VARY

NOW I WANT 3 RECORDS

 SELECT TOP 3 * FROM employee ORDER BY NEWID()

--you will get 3 random records from the table

NOW I WANT THE TOP 3 RECORDS AND THAT TOO ORDER BY SAP ID DESC

 SELECT TOP 3 * FROM employee

 ORDER BY SAPID DESC,NEWID()

HCLDEP      SAPID       NAME        GENDER      project

3           888         MUKUA       MALE        NULL

4           888         RITA        FEMALE      NULL

2           750         PRACHI      FEMALE      NULL

NOW I WANT THE TOP 3 RECORDS AND THAT TOO ORDER BY SAP ID ASC

 SELECT TOP 3 * FROM employee

 ORDER BY SAPID ASC,NEWID()

HCLDEP      SAPID       NAME        GENDER      project

3           243         BIKASH      MALE        NULL

2           325         SIBA        MALE        NULL

2           500         RUCHI       FEMALE      NULL

RAND DOESNOT WORK

--RAND() THIS GIVES ONLY ONE VALUE...SO THIS DOESNOT WORK

SELECT TOP 1 * FROM EMPLOYEE ORDER BY RAND()

TABLE SPAN

--FOR LARGER TABLE USE TABLESAMPLE TO AVOID SCANNING OF WHOLE TABLE

--SUPPOSE IF EMPLOYEE TABLE HAS 20000 RECORDS

SELECT  TOP 1 *

FROM EMPLOYEE

TABLESAMPLE (1000 ROWS)

ORDER BY NEWID()

====== J J J J J J J J J J J J J J J J J J J J J J J J J J J ======

                                                                         SEE YOU