none
Selecting random unique records RRS feed

  • Question

  • i have a stored procedure behind an ad rotator, it selects 3 adverts one for each banner position. There are position fields (pos1, pos2, pos3)
    boolean which store which banners the ad can fit. the stored procedure selects one advert for each position. but i need to ensure that they are unique and the same ad isnt selected twise.

    my stored procedure is just 3 selects atm

    BEGIN

    Select top 1 AdID, NavUrl, ImageUrl
    FROM Adverts
    Where Pos1 = 1
    ORDER BY NEWID() 

    Select top 1 AdID, NavUrl, ImageUrl
    FROM Adverts
    Where Pos2 = 1
    ORDER BY NEWID() 

    Select top 1 AdID, NavUrl, ImageUrl
    FROM Adverts
    Where Pos3 = 1
    ORDER BY NEWID() 

    END

    how can i check that the adverts selected are not the same? or ensure it selects 3 different ones?

    thnks



    Tuesday, December 23, 2008 2:41 PM

Answers

  • This should do the trick:

    DECLARE @MyTable    TABLE 
                        (    
                        Pos         INT,   
                        AdID        INT,   
                        NavURL      VARCHAR(MAX),   
                        ImageURL    VARCHAR(MAX)  
                        )  
                          
    INSERT INTO @MyTable(Pos, AdID, NavURL, ImageURL)  
    SELECT TOP 1 1, AdID, NavURL, ImageURL  
    FROM Adverts  
    WHERE Pos1 = 1  
    ORDER BY NEWID()  
     
    INSERT INTO @MyTable(Pos, AdID, NavURL, ImageURL)  
    SELECT TOP 1 2, AdID, NavURL, ImageURL  
    FROM Adverts  
    WHERE Pos2 = 1  
        AND AdID NOT IN(SELECT AdID FROM @MyTable)  
    ORDER BY NEWID()  
     
    INSERT INTO @MyTable(Pos, AdID, NavURL, ImageURL)  
    SELECT TOP 1 3, AdID, NavURL, ImageURL  
    FROM Adverts  
    WHERE Pos3 = 1  
        AND AdID NOT IN(SELECT AdID FROM @MyTable)  
    ORDER BY NEWID()  
     
    SELECT AdID, NavURL, ImageURL  
    FROM @MyTable
    WHERE Pos = 1  
     
    SELECT AdID, NavURL, ImageURL  
    FROM @MyTable
    WHERE Pos = 2  
     
    SELECT AdID, NavURL, ImageURL  
    FROM @MyTable
    WHERE Pos = 3 

    Chris
    MCTS : SQL Server 2005 ; MCITP : Database Developer
    • Marked as answer by Ballinge Tuesday, December 23, 2008 3:37 PM
    Tuesday, December 23, 2008 3:22 PM

All replies

  • Here is the general idea..  you should build your code yourself though cause you don't provide much structure info :
    1. Create a temp table or a table variable to store the search content (further named temp table although not necessarily a temp table)
    2. Loop and on each loop select a random id from your ad table
    3. Check random generated id exists in a temp table
    4. If id not found add it to the temp table
    5. Loop until 3 found
    6. select ads from ad table inner joining on temp table results
    Fast since it requires no cursor, recommend use table variable, faster than temp table and finally can all be planned for faster execution.

    Groupe-CDGI Developper
    Tuesday, December 23, 2008 2:45 PM
  • This should do the trick:

    DECLARE @MyTable    TABLE 
                        (    
                        Pos         INT,   
                        AdID        INT,   
                        NavURL      VARCHAR(MAX),   
                        ImageURL    VARCHAR(MAX)  
                        )  
                          
    INSERT INTO @MyTable(Pos, AdID, NavURL, ImageURL)  
    SELECT TOP 1 1, AdID, NavURL, ImageURL  
    FROM Adverts  
    WHERE Pos1 = 1  
    ORDER BY NEWID()  
     
    INSERT INTO @MyTable(Pos, AdID, NavURL, ImageURL)  
    SELECT TOP 1 2, AdID, NavURL, ImageURL  
    FROM Adverts  
    WHERE Pos2 = 1  
        AND AdID NOT IN(SELECT AdID FROM @MyTable)  
    ORDER BY NEWID()  
     
    INSERT INTO @MyTable(Pos, AdID, NavURL, ImageURL)  
    SELECT TOP 1 3, AdID, NavURL, ImageURL  
    FROM Adverts  
    WHERE Pos3 = 1  
        AND AdID NOT IN(SELECT AdID FROM @MyTable)  
    ORDER BY NEWID()  
     
    SELECT AdID, NavURL, ImageURL  
    FROM @MyTable
    WHERE Pos = 1  
     
    SELECT AdID, NavURL, ImageURL  
    FROM @MyTable
    WHERE Pos = 2  
     
    SELECT AdID, NavURL, ImageURL  
    FROM @MyTable
    WHERE Pos = 3 

    Chris
    MCTS : SQL Server 2005 ; MCITP : Database Developer
    • Marked as answer by Ballinge Tuesday, December 23, 2008 3:37 PM
    Tuesday, December 23, 2008 3:22 PM