Queries to find duplicate rows in a database and  to delete them.   Can anyone explain these queries how they are fetching the result ?  SELECT       *  FROM       emp a  WHERE       rowid = (SELECT MAX(rowid)                FROM EMP b                WHERE a.empno=b.empno)     to Delete:   DELETE FROM       emp a  WHERE       rowid != (SELECT MAX(rowid)                 FROM emp b                 WHERE a.empno=b.empno)
2625
What is the best way to remove duplicate rows from a fairly large SQL Server table (i.e. 300,000+ rows)?  The rows, of course, will not be perfect duplicates because of the existence of the RowID identity field.  MyTable  RowID int not null identity(1,1) primary key, Col1 varchar(20) not null, Col2 varchar(2048) not null, Col3 tinyint not null
108409
When I write a query like this...  select * from table1 t1 join table2 t2 on t1.id = t2.id   Does the SQL optimizer, not sure if that is the correct term, translate that to...  select * from table1 t1, table2 t2 where t1.id = t2.id   Essentially, is the Join statement in SQL Server just an easier way to write sql? Or is it actually used at run-time?  Edit: I almost always, and will almost always, use the Join syntax. I am just curious what happens.
26688
I need to delete some rows from one large table. The rows to delete shouldn't be in another table, example:  DELETE FROM LargeTable WHERE id IS NOT IN (SELECT DISTINCT foreign_id from EvenLargerTable)  but my server can't handle such blunt query, because there are almost a million records in the LargeTable and few million records in the EvenLargerTable  How can I solve it?
15193
I'm testing something in Oracle and populated a table with some sample data, but in the process I accidentally loaded duplicate records, so now I can't create a primary key using some of the columns.  How can I delete all duplicate rows and leave only one of them?
81999
What's the most efficient way to select the last n number of rows in a table using mySQL? The table contains millions of rows, and at any given time I don't know how large the table is (it is constantly growing). The table does have a column that is automatically incremented and used as a unique identifier for each row.
67080
I have a table with 5 columnsID,CPT4,CPT4Mod,ItemCharge,Eff_Date.   Deletion Condition:  When rows exists with duplicate CPT4and CPT4Mod field values but different ItemCharge and Eff_Date values, exclude all but the most recent Eff_Date records.  I need a query which can achieve this in two scenarios.   When CPT4Mod is given by the user. When  CPT4Mod is not given by the user.   Later I need to achieve the same using LINQ.
81760
Do any queries exist that require RIGHT JOIN, or can they always be re-written with LEFT JOIN?  And more specifically, how do you re-write this one without the right join (and I guess implicitly without any subqueries or other fanciness):   SELECT * FROM t1 LEFT JOIN t2 ON t1.k2 = t2.k2 RIGHT JOIN t3 ON t3.k3 = t2.k3
21234
I am wondering if there is away (possibly a better way) to order by the order of the values in an IN() clause.  The problem is that I have 2 queries, one that gets all of the IDs and the second that retrieves all the information. The first creates the order of the IDs which I want the second to order by. The IDs are put in an IN() clause in the correct order.  So it'd be something like (extremely simplified):  SELECT id FROM table1 WHERE ... ORDER BY display_order, name  SELECT name, description, ... WHERE id IN ([id's from first])   The issue is that the second query does not return the results in the same order that the IDs are put into the IN() clause.  One solution I have found is to put all of the IDs into a temp table with an auto incrementing field which is then joined into the second query.  Is there a better option?  Note: As the first query is run "by the user" and the second is run in a background process, there is no way to combine the 2 into 1 query using sub queries.  I am using MySQL, but I'm thinking it might be useful to have it noted what options there are for other DBs as well.
70060
My boss had a query from a customer yesterday asking how they could find out who deleted some data in their SQL Server database (it is the express edition if that matters).  I thought this could be found from the transaction log (providing it hadn't been truncated) - is this correct? And if so how do you actually go about finding this information out?
65404
I have a large amount of duplicate records (25.000) in an even larger table (250.000 records). I've done the detection using the reporting function. The criteria I used to detect the duplicates were 3 fields (unique account identifier, unique user identifier and date). Based on a list of criteria, I would like to either merge or delete these records. I've got a handle on the logic associated with the merging and the deleting but what I'm having trouble with is defining the duplicate detection process.   I'm looking at either creating a custom object to take care of the detection or dumping the records into an SQL database and taking care of it offline. Any thoughts on which one of the two is the best approach?  Edit:  After reading the first comment, I think the more appropriate question would be "How do I approach the problem in SOQL".  The object I'm working with is the Event object and I'm really only looking at the standard fields, AccountId, OwnerId and ActivityDate. From what I've found online, there's no easy way to find records that have the same value for these three fields. I think it could be accomplished by nesting SOQL queries but I'd like to use a different approach if at all possible.
35819
I learned something simple about SQL the other day:  SELECT c FROM myTbl GROUP BY C   Has the same result as:  SELECT DISTINCT C FROM myTbl   What I am curious of, is there anything different in the way an SQL engine processes the command, or are they truly the same thing?    I personally prefer the distinct syntax, but I am sure it's more out of habit than anything else.  EDIT: This is not a question about aggregates. The use of GROUP BY with aggregate functions is understood.
52799
In Sqlite 3 I'm trying to figure out how to select rows based on a minimum value.  I think that I'm limited by not knowing enough of the related terminology to effectively search google.  The table looks like:  num         text        num2       ----------  ----------  ---------- 0           a           1          0           a           2          1           a           3          1           b           4            I want to get the rows where num2 is 1, 2, and 4.  I want to do the selection based on the minimum value of num for each unique value of the text column.  So, for text = 'a', the minimum value of numis 0, so I want rows 1 and 2.  For text = 'b', the minimum value of num is 1, so I want row 4.  Using various combination of group by, I'm able to get either rows 1 and 2 or rows 1 and 4.  I feel like I'm missing a SQL component that would do what I want, but I haven't been able to figure out what it could be.  What is the proper way to do this type of query?  Possible Solution  I've found a way to do this.  I'm not reputable enough to answer my own question, so I'm doing the update here.  I'm not sure if it is always correct or what the efficiency is like.  Any comments are welcome.  I used a compound select statement, where one query finds the minimum value of num for each unique value of text:  sqlite&gt; select num, text from t group by text having num = min( num ); num         text       ----------  ---------- 0           a          1           b            Then I joined this with the full table to get all rows matching these two columns.  sqlite&gt; with u as       ( select num, text from t group by text having num = min( num ) )         select t.* from t join u on t.num = u.num and t.text = u.text; num         text        num2       ----------  ----------  ---------- 0           a           1          0           a           2          1           b           4
18546
When I have a query that checks if a column of type uniqueidentifer does not exist in a table that has a null value then I get no results back. If the subquery does not return a null it works fine and it only happens when using not in.   I know I can just do a not null check in my subquery, but I am curious why this does not work.  Query Example:  select a.guid from tableA a where a.guid not in (select b.guid from tableB AS b)   Working Test:  select 1 where newid() not in (select newid())   Broken Test:  select 1 where newid() not in (select null)
61005
If I have an UPDATE statement that does not actually change any data (because the data is already in the updated state). Is there any performance benefit in putting a check in the WHERE clause to prevent the update?  For example would there be any difference in execution speed between UPDATE 1 and UPDATE 2 in the following:  CREATE TABLE MyTable (ID int PRIMARY KEY, Value int); INSERT INTO MyTable (ID, Value) VALUES     (1, 1),     (2, 2),     (3, 3);  -- UPDATE 1 UPDATE MyTable SET     Value = 2 WHERE     ID = 2     AND Value &lt;&gt; 2; SELECT @@ROWCOUNT;  -- UPDATE 2 UPDATE MyTable SET     Value = 2 WHERE     ID = 2; SELECT @@ROWCOUNT;  DROP TABLE MyTable;   The reason I ask is that I need the row count to include the unchanged row so I know whether to do an insert if the ID does not exist. As such I used the UPDATE 2 form. If there is a performance benefit to using the UPDATE 1 form, is it possible to get the row count that I need somehow?
84603
Say I have an ID row (int) in a database set as the primary key. If I query off the ID often do I also need to index it? Or does it being a primary key mean it's already indexed?  Reason I ask is because in MS SQL Server I can create an index on this ID, which as I stated is my primary key.  Edit: an additional question - will it do any harm to additionally index the primary key?
42749
If i run below query it returns all records of that object:  Select ID, name from Contact where Name like '%%%'   This should return only those records which have % in the name. For example if i run below query it will return only those records which have test in the name.  Select ID, name from Contact where Name like '%test%'   Any idea, What is going on here?
94559
I was just told that I can't run another query:   Too many queries are running, you may only run 2 queries at a time  But I was only running one. Why did it tell  me this? Does running one for Stack Overflow count as 2?