Delete top 1 record??

Question

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Please elaborate on your problem

are you trying to delete duplicates?

does your table have a primary key?

Please show us an example of what your table looks like,

and what your data looks like, and which row you actually want to delete.

Noman Tariq SSCommitted Points: 1750 More actions · Answer 2

Thanx for ur prompt reply.. actually i want only first row. there is no PK. the data looks like:

100 0.0 98.0555555555555

100 1.0 1.0

103 1.0 98.0555555555555

Ray M SSC-Insane Points: 21093 More actions · Answer 3

But what makes you want to pick the first row?

is it because the second value is zero? the combination of the three values?

you just want to delete the first row? regardless of the values?

create table #T1 (Field1 smallint, field2 decimal(4,2), field3 decimal(12,10))

insert into #t1

select 100, 0.0, 98.0555555555555

union

select 100, 1.0, 1.0

union

select 101, 0.0, 98.0555555555555

delete A

from #t1 A

join (select top 1 Field1, Field2, field3

from #t1) DT on dt.Field1 = A.field1

and dt.Field2 = A.field2

and dt.Field3 = A.field3

select *

from #t1

Results

100

1.00 1.0000000000 101

.00 98.0555555556

Noman Tariq SSCommitted Points: 1750 More actions · Answer 4

Thanx boss, It worked...

You r genious..

Best of luck:

Noman

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 5

SET ROWCOUNT 1

DELETE FROM #t1

SET ROWCOUNT 0

Kindest Regards,

Amit Lohia

paramind SSCrazy Points: 2160 More actions · Answer 6

There's a new feature in SQL-2005: DELETE TOP (n). That's for you. I hate to manipulate data this way. This seems to me a lack of good db design.

_/_/_/ paramind _/_/_/