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. Su
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
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
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 A
On Fri, Sep 26, 2008 at 3:25 PM, Steve Midgley <[EMAIL PROTECTED]> wrote:
> This returns an error:
>
> ERROR: column "dummy.id" must appear in the GROUP BY clause or be used in an
> aggregate function
> SQL state: 42803
Oops that what I get for trying air code :(
This works instead:
SELECT D1.*
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 COUNT(
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) +
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
>
Midgley" <[EMAIL PROTECTED]>
To:
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/
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 dup
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
11 matches
Mail list logo
