Hi,
what about this query
It looks strange but is really simple
SELECT
R5.SUMING, COUNT(*)
FROM
(
SELECT
(SELECT CAST(LIST(R3.ROUTE_INDEX || '_' || R3.LINK_NODE) AS VARCHAR(1000)) AS
SUMING FROM (SELECT * FROM ROUTES R4 WHERE R4.NODE=R2.NODE ORDER BY
R4.ROUTE_INDEX ASC) R3), R2.NODE
FROM
(SELECT DISTINCT R.NODE from ROUTES R) R2
) R5
WHERE
EXISTS(
SELECT
*
FROM
(
SELECT
(SELECT CAST(LIST(R3.ROUTE_INDEX || '_' || R3.LINK_NODE) AS VARCHAR(1000)) AS
SUMING FROM (SELECT * FROM ROUTES R4 WHERE R4.NODE=R2.NODE ORDER BY
R4.ROUTE_INDEX ASC) R3)
FROM
(SELECT DISTINCT R.NODE from ROUTES R) R2
) R6
WHERE
R6.SUMING=CAST((SELECT LIST(R3.ROUTE_INDEX || '_' || R3.LINK_NODE) FROM (SELECT
* FROM ROUTES R4 WHERE R4.NODE=R5.NODE ORDER BY R4.ROUTE_INDEX ASC) R3) AS
VARCHAR(1000))
)
GROUP BY 1
HAVING COUNT(*)>1
for me it found all duplicates in sets
regards,
Karol Bieniaszewski
From: 'Daniel Miller' dmil...@amfes.com [firebird-support]
Sent: Thursday, September 28, 2017 10:49 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] LIST gives inconsistent results
It's certainly possible my database is corrupted - I don't think it is. But
I'm willing to test if someone tells me how. However...
At the moment, after several painful hours, I think I've determined the
following:
CREATE TABLE NODES
(
NODE smallint NOT NULL,
ROUTE_UPDATED timestamp,
CONSTRAINT PK_NODES PRIMARY KEY (NODE)
);
CREATE TABLE ROUTES
(
NODE smallint NOT NULL,
ROUTE_INDEX smallint NOT NULL,
LINK_NODE smallint NOT NULL,
QUALITY smallint,
CONSTRAINT PK_ROUTES PRIMARY KEY (NODE,ROUTE_INDEX)
);
Entries in table ROUTES are inserted in primary key order - as well as sorted
by primary key. So via both "raw & natural" order and an active ORDER BY it
shouldn't be that difficult to have a sorted list of routes!
if I do:
select R.NODE, list(R.ROUTE_INDEX||R.LINK_NODE||R.QUALITY) LISTED_ROUTE
from ROUTES R
group by R.NODE
I get a computed column that is properly sorted. I don't even need to specify
an ORDER BY. But...
select N.NODE, list(ROUTE_INDEX||LINK_NODE||QUALITY) LISTED_ROUTE
from NODES N
join (select R.NODE, R.ROUTE_INDEX, R.LINK_NODE, R.QUALITY from ROUTES R
order by R.NODE,R.ROUTE_INDEX) using (NODE)
group by N.NODE
Even though I'm explicitly sorting the source derived table for the LIST - I
get a set of results that seems almost random. Absolutely maddening.
--
Daniel
[Non-text portions of this message have been removed]
