Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2009
    Posts
    3

    Unanswered: select random rows in sql

    hello friends

    i am working with sql server 2000.i am having 24 lacs rows in an output.i want a query to select first 10 lacs rows then from 10 lacs to 20 lacs and then 20 lacs to 24 lacs in order by paste them in excel 2007.kindly help me out or if you have any other method then please reply..

    vipin

  2. #2
    Join Date
    Oct 2009
    Posts
    27
    You can do this in 2 ways,

    First:

    CREATE TABLE Customers(CompanyName VARCHAR(100),Address VARCHAR(300))

    INSERT INTO Customers VALUES('Alfreds Futterkiste','Obere Str. 57')
    INSERT INTO Customers VALUES('Ana Trujillo Emparedados y helados','Avda. de la Constitución 2222')
    INSERT INTO Customers VALUES('Antonio Moreno Taqueria','Mataderos 2312')
    INSERT INTO Customers VALUES('Around the Horn','120 Hanover Sq.')
    INSERT INTO Customers VALUES('Berglunds snabbkop','Berguvsvägen 8')
    INSERT INTO Customers VALUES('Blauer See Delikatessen','Forsterstr. 57')
    INSERT INTO Customers VALUES('Blondesddsl pere et fils','24, place Kleber')
    INSERT INTO Customers VALUES('Bólido Comidas preparadas','C/ Araquil, 67')
    INSERT INTO Customers VALUES('Bottom-Dollar Markets','12, rue des Bouchers')
    INSERT INTO Customers VALUES('Bon app','23 Tsawassen Blvd.')
    INSERT INTO Customers VALUES('B''s Beverages','Fauntleroy Circus')
    INSERT INTO Customers VALUES('Cactus Comidas para llevar','Cerrito 333')
    INSERT INTO Customers VALUES('Centro comercial Moctezuma','Sierras de Granada 9993')

    The following query will give you the specified rows using Row number,

    SELECT * FROM (SELECT (SELECT count(*) FROM Customers AS c2
    WHERE c2.CompanyName <= c1.CompanyName) AS ROWNO, c1.CompanyName
    FROM Customers AS c1) AS CUST
    WHERE ROWNO<=10 ORDER BY CompanyName


    By this way you can pass the ROWNO as per your need and select the rows.
    Your conditions are
    CASE 1: ROWNO<=1000000
    CASE 2: ROWNO>=1000000 AND ROWNO<=2000000
    CASE 3: ROWNO>=2000000

    Second:

    Create a temp table with identity column and pass the select query to that table it generates the RowNumber for each row. Then use that identity column in the where condition.(Same like the above example)


    __________________
    SQL Server Programmers and Consultants
    Microsoft SQL Server Consultants and Developers - SQL Programmers Chicago

  3. #3
    Join Date
    Dec 2009
    Posts
    3

    select random rows in sql

    thanks for the reply but i am getting repeated rows.my format of output is
    circlename, branchcode,branchname,customername,customerd,acid, balance
    i am having same customername in same branch and i am getting same row no. for both the records.

  4. #4
    Join Date
    Oct 2009
    Posts
    27
    Can you try the second option. In the second option you can get different Row numbers.


    SQL Server Programmers and Consultants
    Microsoft SQL Server Consultants and Developers - SQL Programmers Chicago

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why does this have to be random..?

    Why not use a combination of TOP commands and ORDER BY your unique field(s)?
    Code:
    --First 1M
    SELECT TOP 1000000
           field_a
         , field_b
         , ...
         , field_n
    FROM   dbo.your_table
    ORDER
        BY field_a ASC
    
    --Second 1M
    SELECT TOP 1000000
           field_a
         , field_b
         , ...
         , field_n
    FROM   (
            SELECT TOP 2000000
                   field_a
                 , field_b
                 , ...
                 , field_n
            FROM   dbo.your_table
            ORDER
                BY field_a ASC
           ) As a_derived_table
    ORDER
        BY field_a DESC
    
    --Last 400K
    SELECT TOP 400000
           field_a
         , field_b
         , ...
         , field_n
    FROM   dbo.your_table
    ORDER
        BY field_a DESC
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •