tag:blogger.com,1999:blog-6082652835152798567.post2481258791157489627..comments2024-03-28T21:41:58.974-07:00Comments on Sql server, .net and c# video tutorial: Part 4 - Delete duplicate rows in sqlUnknownnoreply@blogger.comBlogger15125tag:blogger.com,1999:blog-6082652835152798567.post-69525314867459511402020-10-10T06:07:56.320-07:002020-10-10T06:07:56.320-07:00with cte_emp as
(
select *, row_number() over (pa...with cte_emp as <br />(<br />select *, row_number() over (partition by firstname order by salary desc) as orderid<br />from employees<br />)<br />delete from cte_emp where orderid > 1 <br />select * from employeesarchanahttps://www.blogger.com/profile/04608858441280974829noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-63594874894942218702020-10-09T18:55:09.909-07:002020-10-09T18:55:09.909-07:00with cte_emp as
(
select *, row_number() over (pa...with cte_emp as <br />(<br />select *, row_number() over (partition by firstname order by salary desc) as orderid<br />from employees<br />)<br />delete from cte_emp where orderid > 1 archanahttps://www.blogger.com/profile/04608858441280974829noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-85534174808485943282020-09-27T09:54:27.567-07:002020-09-27T09:54:27.567-07:00lets think about your scenario change
1) if there...lets think about your scenario change <br />1) if there is multiple duplicate rows in table base on Name example :<br />(1, 'Mark', 'Hastings', 'Male', 60000)<br />(2, 'Mark', 'Hastings', 'Male', 50000)<br />(3, 'Mark', 'Hastings', 'Male', 80000)<br />from this duplicate rows just keep highest salary values row only and others delete?<br />2) if there is multiple duplicate rows in table base on Name example :<br />(1, 'Mark', 'Hastings', 'Male', 60000)<br />(2, 'Mark', 'Hastings', 'Male', 60000)<br />from this duplicate rows just keep latest ID values row only and delete others rows?<br />Yam Basnethttps://www.blogger.com/profile/17355845169744695892noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-50828930232733158182019-08-21T06:03:10.207-07:002019-08-21T06:03:10.207-07:00That's because you don't have the table &q...That's because you don't have the table "test.employeescte" . It says "doesn't exist". The table called "employeescte" belongs to author's database(as an example). So you must replace the code with your table name which has duplicated rows. Don't forget to replace "test" with your name of database.Anonymoushttps://www.blogger.com/profile/07055914343553071692noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-56272098337747373072018-09-19T06:58:33.006-07:002018-09-19T06:58:33.006-07:00In Mysql getting error
Table 'test.employeesc...In Mysql getting error <br />Table 'test.employeescte' doesn't exist<br /><br />Can anyone help me how can i delete duplicate rows in mysql ?Manoj Kumarhttps://www.blogger.com/profile/13525559712862562270noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-67450915063853753862017-07-19T04:31:49.428-07:002017-07-19T04:31:49.428-07:00Excellent .. Great .... Thank you so muchExcellent .. Great .... Thank you so muchMohamed Suleimanhttps://www.blogger.com/profile/03478797173856073229noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-22556795164483691672017-07-04T23:29:29.777-07:002017-07-04T23:29:29.777-07:00i want to use this query in fronnt end .in c# or a...i want to use this query in fronnt end .in c# or asp.net forms..plz help me.how to use<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-72367738663061149472017-03-19T23:26:40.132-07:002017-03-19T23:26:40.132-07:00SELECT DISTINCT * FROM EmployeesSELECT DISTINCT * FROM EmployeesMd Sabbir Shikderhttps://www.facebook.com/md.sabbirshikdertamimnoreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-1609548606361274522017-02-24T09:36:07.864-08:002017-02-24T09:36:07.864-08:00Run this query, it will clear a lot about Rank, De...Run this query, it will clear a lot about Rank, DenseRank and RowNumber<br /><br />select ID, ROW_NUMBER() OVER (partition by ID Order By Id desc) as RowNumberCol,<br /> RANK() OVER(Order By Id desc) as RankCol,<br /> DENSE_RANK() OVER(Order By Id desc) as DenseRankCol<br /> from Employeeshimanshu pareekhttps://www.blogger.com/profile/16585764404152784870noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-49132437192527399042016-11-17T22:27:59.231-08:002016-11-17T22:27:59.231-08:00Hi Venkat,
Can you pls explain how to delete ro...Hi Venkat,<br /> Can you pls explain how to delete rows except one based on only some column values? <br />eg Employees table having FirstName, LastName, Salary & City column. <br />I want to delete all rows except one with FirstName, LastName and City duplicate having salary different. <br />Thanks in advance. Umeshhttp://umeshwithyou.wordpress.comnoreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-2090528844319156732016-03-03T22:24:15.528-08:002016-03-03T22:24:15.528-08:00Will not work as you cant have Min(ID) in select c...Will not work as you cant have Min(ID) in select clause, when its not in Group By clauseAnonymoushttps://www.blogger.com/profile/05732583911732113546noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-36965528008111264332016-02-07T05:03:33.779-08:002016-02-07T05:03:33.779-08:00useful when id is primary keyuseful when id is primary keyAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-16096425029136214072016-02-07T03:57:34.175-08:002016-02-07T03:57:34.175-08:00check this please
SELECT DISTINCT * INTO tblEmpl...check this please <br /><br />SELECT DISTINCT * INTO tblEmployee5<br />FROM tblEmployee<br />DROP TABLE tblEmployee<br />EXEC sp_rename 'tblEmployee5', 'tblEmployee'Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-619856780449414372016-02-07T03:41:02.831-08:002016-02-07T03:41:02.831-08:00this work also
DELETE FROM Employees WHERE id NO...this work also <br /><br />DELETE FROM Employees WHERE id NOT IN<br />(SELECT MIN(id) FROM Employees<br />GROUP BY FirstName,LastName,Gender,Salary)Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-32234699945587049522014-11-22T06:58:26.460-08:002014-11-22T06:58:26.460-08:00SELECT * FROM Employees
-- Delete duplicate rows...SELECT * FROM Employees<br /><br />-- Delete duplicate rows in sql<br /><br />SELECT DISTINCT ID,FirstName,LastName,Gender,Salary<br />FROM<br /> EmployeesAnonymousnoreply@blogger.com