Get first occurrence of a row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Get first occurrence of a row

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2008-05-07 : 11:54:27

Here's my table:

-------------------------------
Orders
-------------------------------
UserID | Cost | Item
-------------------------------

Users can make several different orders. I want to only select the row with the first occurrence of a userID. For example:

-------------------------------
Orders
-------------------------------
UserID | Cost | Item
-------------------------------
1 | 3.00 | Box
1 | 6.00 | Candy
2 | 7.00 | Towel
3 | 5.00 | Flower
3 | 6.00 | Candy

Should only select:
1 | 3.00 | Box
2 | 7.00 | Towel
3 | 5.00 | Flower

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-07 : 12:00:20

in sql 2005:-

SELECT t.UserID,
t.Cost,
t.Item
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY UserID) AS RowNo,
UserID,
Cost,
Item
FROM Orders)t
WHERE t.RowNo=1

In sql 2000:-

SELECT IDENTITY(int,1,1) AS ID,
       UserID,Cost,Item
INTO   #Temp
FROM Orders

SELECT t.* 
FROM #Temp t
INNER JOIN (SELECT UserID,MIN(ID) AS MinID
            FROM #Temp
            GROUP BY UserID)tmp
ON tmp.UserID=t.UserID
AND tmp.MinID=t.ID

nr
SQLTeam MVY

12543 Posts

Posted - 2008-05-07 : 12:24:23

What do you mnean by first?
i.e. which of

1 | 3.00 | Box
1 | 6.00 | Candy

is first? Howq do you order the rows?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Apples
Posting Yak Master

146 Posts

Posted - 2008-05-07 : 12:29:04

visakh, that didn't work for me, is there another way?

nr, the one on top is first. So by first, I mean the row that comes first, the first inserted row.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-07 : 12:35:43

quote:
Originally posted by Apples

visakh, that didn't work for me, is there another way?

nr, the one on top is first. So by first, I mean the row that comes first, the first inserted row.

Why what was the error?

Apples
Posting Yak Master

146 Posts

Posted - 2008-05-07 : 12:41:29

There was no error, it just wasn't returning the first occurrence of the userID for all of them; some would return the right rows, but others would not.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-07 : 12:51:09

quote:
Originally posted by Apples

There was no error, it just wasn't returning the first occurrence of the userID for all of them; some would return the right rows, but others would not.

That is because you cant guarantee the order in which the records are retrieved from the table. Unless you specify a specific order of retrieval from the table based on which you want first occurance by means of an ORDER BY, you cant guarantee what record will be retured as first occurance.

Apples
Posting Yak Master

146 Posts

Posted - 2008-05-07 : 12:52:02

Ah, I see, thanks visakh

nr
SQLTeam MVY

12543 Posts

Posted - 2008-05-07 : 13:53:06

quote:
Originally posted by Apples

visakh, that didn't work for me, is there another way?

nr, the one on top is first. So by first, I mean the row that comes first, the first inserted row.

In your structure there's no way of telling the order of insert.
The one on top is a meaningless concept without an order by.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources