SQL Server 2008 how to select top and random record?

I’m using SQL Server 2008, I want select random row record, and the total number of record is depend on another table’s column value, how to do this?

My SQL statement is something like this, but wrong..

  • How to insert data to the database using a list (List<Object>) of objects and modals?
  • Domains in SQL Server?
  • how the db can auto generate uniqueindentifiers?
  • LocalDatabase - Short lived or long lived connection?
  • How can I see the SQL generated by EF db.entityset?
  • Method not found SetDataTypeProperties in custom SSIS component
  • select top b.number a.name, a.link_id 
    from A a 
    left join B b on b.link_id = a.link_id 
    order by newid()

    Here are my tables and the expected result.

    Table A:

    name   link_id 
    james  100
    albert 100
    susan  100
    simon  101
    tom    101    
    fion   101

    Table B:

    link_id   number
    100       2
    101       1

    Expected result:

    • when run 1st time, result may be:

      name   link_id
      james  100
      susan  100
      fion   101
    • 2nd time result may be:

      albert 100
      susan  100
      simon  101
    • 3rd time could be:

      james  100
      albert 100
      fion   101


    Refer to table B, link_id: 100, number: 2

    meaning that Table A should select out 2 random record for link_id = 100

    and need to select 1 random record for link_id=101

    2 Solutions collect form web for “SQL Server 2008 how to select top and random record?”

    You can use the ROW_NUMBER() function:

    SELECT A.name, A.link_id
    FROM dbo.tblA
    ) AS A
    JOIN dbo.tblB AS B
    ON A.link_id = B.link_id
    WHERE A.rn <= B.number;

    Here is a SqlFiddle to show this in action: http://sqlfiddle.com/#!3/92eac/2

    Try this:

    SELECT  a.*
    FROM    b
            SELECT  TOP (b.number) a.*
            FROM    a
            WHERE   a.link_id = b.link_id
            ORDER BY
            ) a

    Also see: SQLFiddle

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.