Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    223

    Unanswered: How to select one record many times?

    The developers of frond end ask to select only one row from one table, but many times, such as 10. That means the output has 10 rows and same.

    Any suggestion will be appreciated.

    ZYT

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Viola

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE numbers(n int)
    GO
    
    DECLARE @n int
    SET @n = 1
    
    WHILE @n < 101
      BEGIN
    	INSERT INTO numbers(n) SELECT @n
    	SELECT @n = @n + 1
      END
    
    
    SELECT * FROM Orders CROSS JOIN numbers WHERE OrderID = 10248 AND n < 11
    GO
    
    SET NOCOUNT OFF
    DROP TABLE numbers
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...or cheat a bit with a system table:
    Code:
    select	master..spt_values.number as RecordNumber,
    	Orders.*
    from	Orders,
    	master..spt_values
    where	master..spt_values.type = 'P'
    	and master..spt_values.number < 10
    	and Orders.OrderID = 10248
    ...though you should really create a SequentialNumbers table in your database for just such purposes.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Honestly: the front end should do that....

  5. #5
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47

    Smile

    Quote Originally Posted by shammat
    Honestly: the front end should do that....
    Agreed. You are sending extra 9 rows of data to the front end. That's a waste of bandwidth
    -----------------
    KH


Posting Permissions

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