Results 1 to 2 of 2

Thread: Same table

  1. #1
    Join Date
    Nov 2006
    Posts
    11

    Unanswered: Same table

    Lets say we have a table that has about 123 000 rows so now from that we wanna select the top 1000 rows that meet a certain criteria. If it happens that the rows meeting criteria are less than 1000, then the table should just be populated with any records adding up to make up the 1000 rows. how do i do this????

    I tried it this way but it's very slow it takes about 2hrs to execute:

    IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID= OBJECT_ID(N'[DBO].[ctblRPH_WD_HandOver_Pool_MaxID]') AND OBJECTPROPERTY(ID, N'IsUserTable') = 1)
    DROP TABLE [DBO].[ctblRPH_WD_HandOver_Pool_MaxID]
    GO

    SELECT DISTINCT ctblRPH_WD_SM_CE_ToColl_TelDetail_1.Entity_ID_Numb er
    INTO ctblRPH_WD_HandOver_Pool_MaxID
    FROM ctblRPH_WD_SM_CE_ToColl_TelDetail_1 INNER JOIN
    ctblRPH_SM_CE_ToColl_Pool ON ctblRPH_WD_SM_CE_ToColl_TelDetail_1.Entity_ID_Numb er = ctblRPH_SM_CE_ToColl_Pool.Entity_ID_Number
    WHERE (ctblRPH_SM_CE_ToColl_Pool.PrevTraceCount > '3')
    GO

    WHILE (SELECT COUNT(DISTINCT Entity_ID_Number)
    FROM ctblRPH_WD_HandOver_Pool_MaxID) < 1000
    BEGIN
    DECLARE @Count Int
    SET @Count = (SELECT COUNT(DISTINCT Entity_ID_Number)
    FROM ctblRPH_WD_HandOver_Pool_MaxID)
    WHILE @Count < 1000
    BEGIN
    INSERT INTO ctblRPH_WD_HandOver_Pool_MaxID
    (Entity_ID_Number)
    SELECT DISTINCT TOP 1 Entity_ID_Number
    FROM ctblRPH_WD_SM_CE_ToColl_TelDetail
    WHERE (NOT (Entity_ID_Number IN
    (SELECT Entity_ID_Number
    FROM ctblRPH_WD_HandOver_Pool_MaxID)))
    SET @Count = (SELECT COUNT(DISTINCT Entity_ID_Number)
    FROM ctblRPH_WD_HandOver_Pool_MaxID)
    END
    END

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use a CASE statement to create an order in your data:
    Code:
    select	top 1000
    	*
    from	ctblRPH_WD_SM_CE_ToColl_TelDetail_1
    	inner join ctblRPH_SM_CE_ToColl_Pool ON ctblRPH_WD_SM_CE_ToColl_TelDetail_1.Entity_ID_Number = ctblRPH_SM_CE_ToColl_Pool.Entity_ID_Number
    order by case when ctblRPH_SM_CE_ToColl_Pool.PrevTraceCount > '3' then 0 else 1 end
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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