18
votes
How can I display twice each stored row without using UNION ALL or a temporary table?
SELECT ta.id, ta.name
FROM emp ta
CROSS JOIN ( VALUES (1), (2) ) tb (id) ;
- 5,043
16
votes
Accepted
SQLite3: Remove duplicates
If you just want to disambiguate two rows with similar content, you can use the ROWID functionality in SQLite3, which helps uniquely identify each row in the table.
Something like this:
DELETE FROM ...
- 3,589
14
votes
Accepted
Multiple on conflict targets
Your example suggests duplicate rows in the VALUES clause itself - which would result in:
ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
Can be solved by folding duplicates ...
- 173k
11
votes
Accepted
Duplicate rows - how to remove one?
This will work I think:
with d as
( select ctid, row_number() over (partition by t.*) as rn
from tablename as t
)
delete from tablename as t
using d
where d.rn > 1
and d.ctid = t....
- 97.4k
11
votes
How can I display twice each stored row without using UNION ALL or a temporary table?
One way would be
SELECT COALESCE(a.id, b.id) AS id,
COALESCE(a.name, b.name) AS name
FROM emp a
FULL OUTER JOIN emp b ON 1=0
ORDER BY id;
- 83.3k
10
votes
Accepted
Selecting only one duplicate
You can use a CTE for this, if you want the row that's returned to be a complete, intact row rather than aggregates of any of the other columns. You can change the ORDER BY to prefer rows by any of ...
- 180k
10
votes
How can I display twice each stored row without using UNION ALL or a temporary table?
Three more ways.
Similar to Paparazzi's answer, making use that a NATURAL join becomes a CROSS join when there is no common column:
SELECT e.id, e.name
FROM emp AS e
NATURAL JOIN
(VALUES (1),...
- 97.4k
10
votes
Accepted
Sum up counts in subquery
Does this do what you want?
SELECT
total =
SUM(x.records)
FROM
(
SELECT
records =
COUNT_BIG(thecol)
FROM thetable
WHERE thecol IS NOT NULL
GROUP ...
- 38.9k
10
votes
Accepted
When should the IGNORE_DUP_KEY option be used on an index?
It's definitely an atypical setting to enable for an index. I don't think you're going to find many people who jump up and talk about using it.
Of course, there are a couple helpful posts on the ...
- 38.9k
9
votes
Accepted
Duplicate records returned from table with no duplicates
There are some tricky scenarios which can result in the same row being read twice from an index, even under the READ COMMITTED isolation level.
Your query does not qualify for an allocation order ...
- 32k
8
votes
When should the IGNORE_DUP_KEY option be used on an index?
Every feature has a use case, and IGNORE_DUP_KEY is no exception.
As I explain in IGNORE_DUP_KEY slower on clustered indexes, this option only potentially benefits performance (on a clustered index) ...
- 83k
7
votes
Unable to create UNIQUE Filtered index with IGNORE_DUP_KEY = ON
As already mentioned by the other answer, IGNORE_DUP_KEY is not supported for filtered indexes, which is explicitly stated in the manual:
Filtered indexes do not allow the IGNORE_DUP_KEY option.
...
- 22.8k
6
votes
Accepted
does INSERT INTO ... ON DUPLICATE KEY UPDATE executes even if the row has not changed?
MySQL executes ON DUPLICATE KEY UPDATE the same way it executes UPDATE statements:
It checks the contents of each row (and columns) to be updated and if they are identical to the supplied, it does ...
- 97.4k
6
votes
How do I insert record only if the record doesn't exist?
You can do this with a unique constraint & insert or ignore.
Create table:
ironforge:tmp phil$ sqlite3
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a ...
- 31.6k
6
votes
Accepted
Eliminate extra rows that intersect
Then you need an < or > instead of a <>.
That is similar to when you try to find dupes in your data
CREATE TABLE dbo.t
(
n varchar(100) NOT NULL
, i geometry NOT NULL
);
INSERT ...
- 7,917
5
votes
Accepted
Exclude All Matching Rows
Is there a way to query into the table and grab only those that have
unique emails, while ignoring those that share emails?
I can think of a couple ways to do this:
1) If I restate this, I might ...
- 16.1k
5
votes
Update Grouped records
You can do this much simpler using a CTE:
;WITH x AS
(
SELECT
Field1, Field2,
c = COUNT(*) OVER(PARTITION BY Field1, Field2)
FROM dbo.Table1
WHERE Field2 <> ''
)
UPDATE x ...
- 180k
5
votes
MySql: check and remove duplicate rows
1. How can I check if a table contains duplicate rows, with the same value of the couple (date, time) ?
SELECT * FROM db.table HAVING COUNT(symbol) > 1
2. How can I remove all but one row ?
a) ...
- 473
5
votes
Accepted
Deduplicate Data Using Having with Subquery
There are restrictions regarding self-joins in MySQL's implementation of UPDATE and DELETE. The issue is usually solvable by using joins:
DELETE g.*
FROM game AS g
JOIN
( SELECT matchid, ...
- 97.4k
5
votes
Accepted
There is a column named in table but it cannot be referenced from this part of the query
First off, partition by A,B,C order by A,B,C makes no sense. Since you intent to keep the "last" row (meaning the one with the greatest ID), you probably meant:
partition by A,B,C order by ID
Still, ...
- 173k
4
votes
How can I merge duplicate rows in a single table?
This is what's known as an aggregate query,
SELECT cart, barcode,
SUM(counted_in) AS counted_in,
SUM(counted_out) AS counted_out
FROM tbl
GROUP BY cart, barcode;
Using GROUP BY like ...
- 9,095
4
votes
Accepted
Show only duplicate values
That can be done with a simple SELECT statement.
SELECT count(*) AS count_ , favorite_color_
FROM person_
GROUP BY favorite_color_
HAVING count(*) > 1
ORDER BY count_ DESC
;
The count(*) ...
- 10.6k
4
votes
Merging two selects then sort by and remove duplicates
UNION is definitely the way to "stack" rowsets on top of each other, while removing duplicates. (Use UNION ALL to keep duplicates.)
Your queries appear to have some cleanup possible, so we'll do that ...
- 4,305
4
votes
Accepted
Want to clone a role, but am getting "can't login" message after doing so
The answer is simple, but may be very surprising:
CREATE USER is now an alias for CREATE ROLE. The only difference is
that when the command is spelled CREATE USER, LOGIN is assumed by
default, ...
- 31k
4
votes
Removing duplicates values with join
Use the next query
SELECT
B.Company,
COUNT(DISTINCT A.Ticket) AS QTY
FROM Table1 A
INNER JOIN Table2 B
ON A.CompanySk = B.EDWCompanySK
WHERE A.StartedDate >= '20171128'
GROUP BY B.Company
ORDER BY ...
- 2,151
4
votes
Accepted
Deleting duplicate based on latest datestamp entry
You can use a CTE to add a row number that starts at 1 from the newest datestamp for each e-mail address, then delete all the rest.
;WITH x AS
(
SELECT email, origin_date, new_opt_in_date, ...
- 180k
4
votes
Accepted
How to handle duplicates in an UPDATE on a primary key in Postgres?
We need move stocks between companies, right? What means we need add stocks to existing users and change company for new users. Or, what the same, we could delete all stocks of company "foo" ...
- 2,804
Only top scored, non community-wiki answers of a minimum length are eligible