At 09:50 PM 9/29/2008, Richard Broersma wrote:
On Mon, Sep 29, 2008 at 7:48 PM, Steve Midgley [EMAIL PROTECTED]
wrote:
In my specific case it turns out I only had duplicates, but there
could have
been n-plicates, so your code is still correct for my use-case
(though I
didn't say that in
On Mon, Sep 29, 2008 at 11:05 PM, Steve Midgley [EMAIL PROTECTED] wrote:
Any reason why using serial PK's with compound
natural unique indices is better/worse than just using natural PK's?
Not really, surrogate keys will always work well so long as unwanted
duplicates are constrained.
At 05:38 PM 9/26/2008, Oliveiros Cristina wrote:
In-Reply-To: [EMAIL PROTECTED]
References: [EMAIL PROTECTED]
[EMAIL PROTECTED]
[EMAIL PROTECTED]
Howdy, Steve.
SELECT id
FROM dummy a
NATURAL JOIN (
SELECT fkey_id,name
FROM dummy
GROUP BY fkey_id,name
HAVING COUNT(*) 1 AND
On Mon, Sep 29, 2008 at 7:48 PM, Steve Midgley [EMAIL PROTECTED] wrote:
In my specific case it turns out I only had duplicates, but there could have
been n-plicates, so your code is still correct for my use-case (though I
didn't say that in my OP).
Ya there are a lot of neat queries that you
Hi,
I've been kicking this around today and I can't think of a way to solve
my problem in pure SQL (i.e. I can only do it with a
looping/cursor-type solution and some variables).
Given a table with this DDL/data script:
drop table if exists dummy;
create table dummy (
id integer primary
On Fri, Sep 26, 2008 at 10:39 AM, Steve Midgley [EMAIL PROTECTED] wrote:
drop table if exists dummy;
create table dummy (
id integer primary key,
name varchar(255),
fkey_id integer
)
;
The system should return
502163
502164
502170
502171
--first get all of the duplicated ids
[EMAIL PROTECTED]
To: pgsql-sql@postgresql.org
Sent: Friday, September 26, 2008 6:39 PM
Subject: [SQL] Finding sequential records
Hi,
I've been kicking this around today and I can't think of a way to solve
my problem in pure SQL (i.e. I can only do it with a
looping/cursor-type solution and some
oops I noticed I forgot the having clause:
SELECT id
FROM Dummy
GROUP BY name, fkey_id
Having count(*) 1;
SELECT A.*
FROM ( SELECT ID
FROM Dummy
GROUP BY name, fkey_id
HAVING count(*) 1 ) AS A
INNER JOIN Dummy AS D
ON
Wow.
Thanks to both Richard and Oliveiros.
Out of the box Oliveiros' solution does what I want but I don't
understand why!
SELECT id
FROM dummy a
NATURAL JOIN (
SELECT fkey_id,name
FROM dummy
GROUP BY fkey_id,name
HAVING COUNT(*) 1 AND SUM(id) = (MAX(id) + MIN(id)) * (MAX(id) -
MIN(id) +
Howdy, Steve.
SELECT id
FROM dummy a
NATURAL JOIN (
SELECT fkey_id,name
FROM dummy
GROUP BY fkey_id,name
HAVING COUNT(*) 1 AND SUM(id) = (MAX(id) + MIN(id)) * (MAX(id) - MIN(id) +
1) / 2
) b
ORDER BY id;
The GROUP BY clause is to associate records that have the same fkey_id and
name
The
10 matches
Mail list logo
