to delete a row from similar row containing table

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

SQL Server 2005 Forums

Transact-SQL (2005)

to delete a row from similar row containing table

Author

Topic

shijiltvsql
Starting Member

4 Posts

Posted - 2009-01-15 : 08:24:23

hai iam new to forums
give instructions and tips to utilise this site properly
i have a table as like
sno sname age
1 aaa 18
2 bbb 19
3 aaa 18

i want to delete only first row by using condition

stv

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 08:27:33

[code]
delete t
from
(
select row_number() over (partition by sname order by sno desc) as seq
from table
)t
where seq >1
[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-15 : 10:51:41

quote:

Originally posted by visakh16


delete t 
from
(
select row_number() over (partition by sname order by sno desc) as seq,* from table
)t
where seq >1

shijiltvsql
Starting Member

4 Posts

Posted - 2009-01-23 : 10:27:30

while checking the query i got an error

'row_number' is not a recognized function name.

and what is seq

can u help me

stv

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-01-23 : 10:36:30

Are you using SQL Server 2005?

If so is the database in 2000 compatability mode?

An infinite universe is the ultimate cartesian product.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-23 : 11:03:14

quote:
Originally posted by shijiltvsql

while checking the query i got an error

'row_number' is not a recognized function name.

and what is seq

can u help me

stv

try this

delete r
from table r
LEFT JOIN (
SELECT MAX(sno) rowno ,sname,age
FROM TABLE
GROUP BY sname,age ) t ON t.rowno = r.sno
WHERE t.rowno IS NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 11:10:18

quote:
Originally posted by sodeep

quote:
Originally posted by visakh16

delete t
from
(
select row_number() over (partition by sname order by sno desc) as seq,* from table
)t
where seq >1

no need of * as i'm deleting

why simply include all columns?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 11:11:53

quote:
Originally posted by raky

quote:
Originally posted by shijiltvsql

while checking the query i got an error

'row_number' is not a recognized function name.

and what is seq

can u help me

stv

try this

delete r
from table r
LEFT JOIN (
SELECT MAX(sno) rowno ,sname,age
FROM TABLE
GROUP BY sname,age ) t ON t.rowno = r.sno
WHERE t.rowno IS NULL

that deletes all except last row
it should be :-


delete r
from table r
INNER JOIN ( 
SELECT MIN(sno) rowno ,sname,age 
FROM TABLE 
GROUP BY sname,age ) t ON t.rowno = r.sno

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 11:13:01

quote:
Originally posted by shijiltvsql

while checking the query i got an error

'row_number' is not a recognized function name.

and what is seq

can u help me

stv

what does this return?


SELECT @@VERSION

EXEC sp_dbcmptlevel yourdb

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-23 : 11:16:24

quote:
Originally posted by visakh16

quote:
Originally posted by raky

quote:
Originally posted by shijiltvsql

while checking the query i got an error

'row_number' is not a recognized function name.

and what is seq

can u help me

stv

try this

delete r
from table r
LEFT JOIN (
SELECT MAX(sno) rowno ,sname,age
FROM TABLE
GROUP BY sname,age ) t ON t.rowno = r.sno
WHERE t.rowno IS NULL

that deletes all except last row
it should be :-

delete r
from table r
INNER JOIN (
SELECT MIN(sno) rowno ,sname,age
FROM TABLE
GROUP BY sname,age ) t ON t.rowno = r.sno

yaa if op have more than two records with same combination of sname,age.My query will delete all rows except the maxid combination.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources