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!

NinaHave you tried SELECT DISTINCT ID FROM ...|||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.|||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.|||Maybe something like:

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|||...that'll work if he can specify a secondary sort order.