Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Unanswered: select case select

    I have this select:
    SELECT CASE productID WHEN 'beee' THEN (SELECT TOP (1) productID
    FROM dbo.subProducts
    WHERE (subGroupID = '90d4e7c4-91ca-4180-84e7-024822b45570')
    ORDER BY NEWID()) ELSE productID END AS ProductID, ProductID AS oldProdid, imageID
    FROM dbo.Products
    WHERE (linkID = 1234')

    samle return:
    ProdID oldProdid linkID
    456 456 1234
    12d4 beee 1234
    12d4 beee 1234
    12d4 beee 1234
    8054 8054 1234

    Each time I run the query the 12d4 sample changes to new ProdID, but they are all the same. I need it to grab a different random id for each row. Any Ideas?

    Thank you!

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I have never used random record select statements, but I know it can be done with ORDER BY NEWID(). I don't see why your script shouldn't work. I did notice a non-matching ' in WHERE (linkID = 1234'). I rewrote it as a UNION ALL.
    Code:
    SELECT productID, 
        ProductID AS oldProdid, 
        imageID
    FROM dbo.Products
    WHERE productID <> 'beee'
        
    UNION ALL
    
    SELECT (SELECT TOP (1) productID
            FROM dbo.subProducts
            WHERE subGroupID = '90d4e7c4-91ca-4180-84e7-024822b45570'
            ORDER BY NEWID()
            ) AS ProductID, 
        ProductID AS oldProdid, 
        imageID
    FROM dbo.Products
    WHERE productID <> 'beee'
    Each time I run the script the 'beee' is replaced with something else, and that "something else" varies from run to run, but the "something else" has always the same value for the whole result set. That indicates that the subselect "SELECT TOP (1) productID .." is only once executed per run.

    Sorry, can't help you any further.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

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