Keep first row in grouped data

Question

Keep first row in grouped data

Barkingdog

SSCoach

Points: 18670
More actions
September 17, 2008 at 4:56 pm

#77089

I have a table whose data is already grouped by the first column:
1 2 7
1 2 5
1 7 3
1 4 9
2 3 4
This first group (column1 = 1) has 4 rows; the second "group" contians a single row
In each group I want to delete all records after the first record in that group. In the above case here's what the end result looks like.
1 2 7 (the first row of the group)
2 3 4 (the first and only row in the singleton gorup).
How to do this in sql 2000 is the question.
TIA,
Barkingdog

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 1

As so many will, undoubtedly, tell you, how do you know what the "first" record really is? There's no guarantee that the order you enter the data will stay that way. There has to be a column or set of columns to sort on to identify what "first" means.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Barkingdog SSCoach Points: 18670 More actions · Answer 2

The first column, not shown, could well be an identity column. So the first row could have ID = 1, the next row 2, etc....

Bill

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 3

In that case, please take a peek at the link in my signature. It'll help use help you faster and better. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Barkingdog SSCoach Points: 18670 More actions · Answer 4

Good URL. I will recast my original "question" using some of the ideas in your article.

Barkingdog