Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Question Unanswered: iterate through a view?

    Hello everybody,

    obviously, I'm a newbie, so please forgive me for using the wrong terms accidently.

    I have created a view that looks like this:

    ID Disc Cut
    ----------------
    1 11 25
    1 5 34
    2 1 67
    3 8 54
    4 5 14
    4 1 12
    ...

    ID is a bigint, Disc & Cut ar varchars

    As you can see, the ID is not the primary key.
    Still, what I need is only the record of the first occurance of each ID,
    like this:

    ID Disc Cut
    ----------------
    1 11 25
    2 1 67
    3 8 54
    4 5 14
    ...

    Is there a way that I can iterate through the view and compare one record to
    the previous or next?
    (I didn't find anything in the Microsoft Library yet...)

    Thanks!

    Nina

  2. #2
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    Have you tried SELECT DISTINCT ID FROM ...

  3. #3
    Join Date
    Feb 2004
    Posts
    2
    Originally posted by grahamt
    Have you tried SELECT DISTINCT ID FROM ...
    That wouldn't work because these rows:

    1 11 25
    1 5 34

    are not distinct but I need only one of them.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There is no such thing as "First" in SQL server unless you specify a sort order. You obviously have your data sorted by ID, but after that SQL Server make no guarantee as to the order in which your data will be returned.

    It could easily be returned like this on some subsequent execution:

    ID Disc Cut
    ----------------
    1 11 25
    1 5 34
    2 1 67
    3 8 54
    4 1 12
    4 5 14

    You will need to specify a secondary sort order in order to select between duplicate ID values.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Maybe something like:

    Code:
    USE Northwind
    GO
    
        SELECT * FROM Orders l
    INNER JOIN (SELECT OrderId, MAX(OrderDate) AS OrderDate 
    	      FROM Orders 
    	  GROUP BY OrderId) AS r
            ON l.OrderId = r.OrderId
           AND l.OrderDate = r.OrderDate
    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.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...that'll work if he can specify a secondary sort order.
    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
  •