Jednoduchý SQL dotaz (pro spidera)

urob union dvoch selectov. jedno ti zgrupuje take, ktorych hodnoty su ciselne a druhe len surovo vypise tie, ktore su neciselne. opat som tam dal funkciu isnumeric, ktora viem, ze v mysql nie je. nahrad ju ekvivalentom (nechcelo sa mi ho hladat).

SELECT products_id, additional_name, min( additional_value ) ,  max( additional_value ) as val, additional_text
FROM products_additional
WHERE TYPE = 'ADI' AND ISNUMERIC(additional_value) = 1
GROUP BY additional_name

UNION ALL

SELECT products_id, additional_name, additional_value,  additional_value as val, additional_text
FROM products_additional
WHERE TYPE = 'ADI' AND ISNUMERIC(additional_value) = 0

Dobře. A zeptám se, nemá tento union ten typ bugu, že zatímco horní část dotazu vytáhne nečíselné stavy do jednoho řádku,
tak spodní část to vytahne do řádků ?

Tedy že tam budu mít duplikované záznamy.

//EDIT chápu Nemá. Musel jsem se zamyslet.

Testnu to a jestli to bude chodit značím vyřešeno .

ak sa bojis duplicit, tak nahrad UNION ALL za UNION.

Mám to takto:

SELECT products_id, additional_name, min( additional_value ) , max( additional_value ) as val, additional_text
FROM products_additional
WHERE TYPE = 'ADI' AND (additional_value regexp '^[0-9]' = 1)
GROUP BY additional_name

UNION

SELECT products_id, additional_name, additional_value, additional_value as val, additional_text
FROM products_additional
WHERE TYPE = 'ADI' AND (additional_value regexp '^[0-9]' = 0)

Ale nepracuje to, jak si představuju.
Nenumerické hodnoty jsou stále v jednom sloupečku:

Hádám, že to bude v tom substitutu za numeric.

Tady kdyžtak přidávám tabulku:

-- Struktura tabulky `products_additional`
--

CREATE TABLE IF NOT EXISTS `products_additional` (
`products_additional_id` int(11) NOT NULL auto_increment,
`type` char(3) collate cp1250_czech_cs NOT NULL,
`products_id` int(11) NOT NULL default '0',
`additional_name` text collate cp1250_czech_cs,
`language_id` int(11) NOT NULL default '0',
`additional_value` varchar(255) collate cp1250_czech_cs NOT NULL,
`additional_text` varchar(128) collate cp1250_czech_cs default NULL,
PRIMARY KEY (`products_additional_id`),
KEY `products_id` (`products_id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1250 COLLATE=cp1250_czech_cs AUTO_INCREMENT=43 ;

--
-- Vypisuji data pro tabulku `products_additional`
--

INSERT INTO `products_additional` (`products_additional_id`, `type`, `products_id`, `additional_name`, `language_id`, `additional_value`, `additional_text`) VALUES
(34, 'ADI', 61500, 'Spotreba', 0, '20', 'Spotřeba'),
(35, 'ADI', 61500, 'Rozliseni', 0, '1280x1024', 'Rozlišení'),
(36, 'ADI', 61507, 'ScanerDPI', 0, '2000', '2000 DPI'),
(37, 'ADI', 61512, 'CPU', 0, 'CPU 4MB', 'CPU 4MB'),
(38, 'ADI', 61500, 'Spotreba', 0, '40', 'Spotřeba'),
(39, 'ADI', 61500, 'Vaha', 0, '10', 'Váha'),
(40, 'ADI', 61500, 'Vaha', 0, '15', 'Váha'),
(41, 'ADI', 61500, 'Rozliseni', 0, '1680x1050', 'Rozlišení'),
(42, 'ADI', 61500, 'Rozliseni', 0, '1920x1080', 'Rozlišení');

Problém byl jen v regulárním výrazu:

SELECT products_id, additional_name, min( additional_value ) , max( additional_value ) as val, additional_text
FROM products_additional
WHERE TYPE = 'ADI' AND (additional_value regexp( '^[0-9]+$') = 1)
GROUP BY additional_name

UNION

SELECT products_id, additional_name, additional_value, additional_value as val, additional_text
FROM products_additional
WHERE TYPE = 'ADI' AND (additional_value regexp ('^[0-9]+$') = 0 )

Díky Spidere !

Ještě poslední dotaz (SQL dotaz ) :

SELECT products_id, additional_name, min( additional_value ) , case when additional_value regexp '^[0-9]' then max( additional_value ) else additional_value end as val, additional_text FROM products_additional WHERE TYPE = 'ADI' AND products_id IN (61501, 61512, 61507, 61500, 61511, 61509, 61510) AND (additional_value regexp( '^[0-9]+$') = 1) GROUP BY additional_name ORDER BY additional_name, additional_value UNION SELECT products_id, additional_name, min( additional_value ) , case when additional_value regexp '^[0-9]' then max( additional_value ) else additional_value end as val, additional_text FROM products_additional WHERE TYPE = 'ADI' AND products_id IN (61501, 61512, 61507, 61500, 61511, 61509, 61510) AND (additional_value regexp( '^[0-9]+$') = 0) GROUP BY additional_name ORDER BY additional_name, additional_value

Je korektní, ale hlásí chybu: Incorrect usage of UNION and ORDER BY

Jak z tohoto ven?

do unionu nie je mozne vlozit klauzulu order by.
urob to dvojkrokovo ... z toho union selectu sprav jedno view a potom select * from vyrobene_view order by co_chces