Вопрос... MySQL

[Hamely]

Есть вот такая задачка

Записи в таблице, одно из полей - номер версии в формате 1.0.0.0 (1.0.0.1, 1.0.2.0, 1.0.10.0 и т.д.) Надо найти запись с макимальный номером версии (т.е. максимум из этих чисел). Вроде элементарно, но.. что-то туплю...
числа но не числа, int? varchar? float? слишком много точек, как и во что их преобразовывать для сравнения.... и всё это желательно сделать в одном запросе

[maxx™]

Есть вот такая задачка

Записи в таблице, одно из полей - номер версии в формате 1.0.0.0 (1.0.0.1, 1.0.2.0, 1.0.10.0 и т.д.) Надо найти запись с макимальный номером версии (т.е. максимум из этих чисел). Вроде элементарно, но.. что-то туплю...
числа но не числа, int? varchar? float? слишком много точек, как и во что их преобразовывать для сравнения.... и всё это желательно сделать в одном запросе

Мне кажется вы их просто храните не правильно. Их бы перед хранением перевести в числовой вид. А так сортировка по тексту должа дать правильный вариант, но это если у вас строго формат соблюдается. Напишите функцию типа versiontoint а потом сделайте select max(versiontoint(version)) as ver from table order by ver desc limit 1.

[Hamely]

сортировка по тексту таки даёт правильный вариант, НО , как Вы и сказали, если соблюдать формат, но вот вдруг формат перестал соблюдаться, одна из версий стала вида 1.0.11.0 и усё ... мне вспомнился вариант "IP to Integer" ( INET_ATON \ INET_NTOA ) по сути это как раз тоже что Вы и предлагаете, только уже есть такая SQL фукция и (мне повезло) мои номера версий совпадают со структурой ip ... попробую оба варианта. Спасибо

[081krieger]

Вам по-любому тут нужны регулярные выражения.
При виде версии ХХ.ХХ.ХХ.ХХ, где ХХ - одно- или двузначное число. Для MySQL, к сожалению, не подскажу, а для Oracle было бы как-то так:
ПРОЦЕДУРА[
create or replace view view1 as select max(to_number(regexp_substr(str, '^[0-9]{1,2}', 1, level))) str from (select vers str from versions) t connect by instr(str, '\.', 1, level -1) >0; -- Выбираем ХХ.ХХ.ХХ.ХХ максимальное
create or replace view view2 as select vers from versions v, view1 w where regexp_like(v.vers, w.str); --Заносим все подходящие
create or replace view view3 as select max(to_number(regexp_substr(str, '\d{1,2}', 2, level))) str from (select vers str from view2) t connect by instr (str, '\.', 1, level -1) >0; -- выбираем максимальное ХХ.ХХ.ХХ.ХХ
create or replace view view4 as select vers from versions v, view1 w1, view3 w3 where regexp_like(v.vers, w1.str ||'.'||w3.str); -- снова соединяем
create or replace view view5 as select max(to_number(regexp_substr(str, '\d{1,2}', 4, level))) str from (select vers str from view4) t connect by instr (str, '.\d{1,2}$', 1, level -1) >0; -- Выбираем максимум из ХХ.ХХ.ХХ.ХХ
create or replace view view6 as select vers from versions v, view1 w1, view3 w3, view5 w5 where regexp_like(v.vers, w1.str ||'.'||w3.str ||'.' || w5.str); --Сохраняем
create or replace view view7 as select max(to_number(regexp_substr(str, '\d{1,2}$', 1, level))) str from (select vers str from view6) t connect by instr (str, '\.', 1, level -1) >0; -- Выбираем их ХХ.ХХ.ХХ.ХХ максимум
create or replace view view8 as select vers from versions v, view1 w1, view3 w3, view5 w5, view7 w7 where regexp_like(v.vers, w1.str||'.'||w3.str||'.'||w5.str||'.'||w7.str); --Тут наша финальная версия
--Достаем из основной таблички
select * from versions v, view8 w where v.vers=(select vers from view8);
]КОНЕЦ ПРОЦЕДУРЫ
При тестировании использовал табличку create table versions(id number not null primary key, 'vers' varchar2(12));
На самом деле, это очень "извращенный вариант". На мой взгляд, намного проще обработать на каком-нибудь программном языке.

[Архон]

сортировка по тексту таки даёт правильный вариант, НО , как Вы и сказали, если соблюдать формат, но вот вдруг формат перестал соблюдаться, одна из версий стала вида 1.0.11.0 и усё

Сортировку по тексту в печь. Получится, что версия 9.10.0.0 или, как вы правильно заметили, 1.0.11.0 старше 10.0.0.0, т. к. упорядочивание идет по коду символов слева на право. Она сработает только, если для каждого из 4 разрядов версии строго задано кол-во позиций и числа дополняются ведущими нулями. Но хранить версии в виде 001.000.110.000, согласитесь, глупо. Так что у вас есть свой формат и он отличный.

Если ваш "IP to Integer" не сработает (пардон, не силен в MySql), то план такой:
1) Извлечь каждый из 4 разрядов версии в отдельный столбец набора, преобразовав текст в число
2) Выполнить сортировку по убыванию по каждому разряду в порядке старшинства
3) Из полученного набора извлечь первую запись - она и будет с макс. номером версии

Кстати, поле версии в вашей таблице уникально?

Если да, то в Oracle 1-м запросом это можно сделать так (привет, 081krieger, будь проще ):

SELECT * /* перечислить только нужные поля */
FROM (
SELECT
To_Number(SubStr(version, 1, InStr(version, '.') - 1)) AS V1,
To_Number(SubStr(version, InStr(version, '.') + 1, InStr(version, '.', 1, 2) - InStr(version, '.') - 1)) AS V2,
To_Number(SubStr(version, InStr(version, '.', 1, 2) + 1, InStr(version, '.', 1, 3) - InStr(version, '.', 1, 2) - 1)) AS V3,
To_Number(SubStr(version, InStr(version, '.', 1, 3) + 1)) AS V4,
T.*
FROM TstTab T
ORDER BY 1 DESC, 2 DESC, 3 DESC ,4 DESC)
WHERE ROWNUM = 1 /* только первая запись */

где,

INSTR(char1, char2[,n[,m]]) - Позиция m-того включения "char2" в "char1" при начале поиска с позиции n. Если m опущено, по умолчанию предполагается 1; аналогично для n. Позиции даются относительно первого знака "char1", даже если n > 1
SUBSTR(char,m [,n]) - Подстрока, получаемая из "char", начиная с символа m. Если задано n, то подстрока ограничивается n символами. При отрицательном m символы отсчитываются с конца "char"..

http://www.codenet.ru/db/mysql5/manual.ru_Reference.php
В MySQl смотрю тоже есть ф-ции работы со строками: SUBSTRING, LOCATE и т. д. - можно поигриться.

Если поле не уникально, можно поизвращаться c той же идеей:

SELECT *
FROM TstTab
WHERE Version = (SELECT Version
FROM (
SELECT
To_Number(SubStr(version, 1, InStr(version, '.') - 1)) AS V1,
To_Number(SubStr(version, InStr(version, '.') + 1, InStr(version, '.', 1, 2) - InStr(version, '.') - 1)) AS V2,
To_Number(SubStr(version, InStr(version, '.', 1, 2) + 1, InStr(version, '.', 1, 3) - InStr(version, '.', 1, 2) - 1)) AS V3,
To_Number(SubStr(version, InStr(version, '.', 1, 3) + 1)) AS V4,
T.Version
FROM TstTab T
ORDER BY 1 DESC, 2 DESC, 3 DESC ,4 DESC)
WHERE ROWNUM = 1)

Ну а для истинных ораклоидов:

WITH T1 AS (
SELECT
T.*,
Rank() over (
ORDER BY
To_Number(SubStr(version, 1, InStr(version, '.') - 1)) DESC,
To_Number(SubStr(version, InStr(version, '.') + 1, InStr(version, '.', 1, 2) - InStr(version, '.') - 1)) DESC ,
To_Number(SubStr(version, InStr(version, '.', 1, 2) + 1, InStr(version, '.', 1, 3) - InStr(version, '.', 1, 2) - 1)) DESC,
To_Number(SubStr(version, InStr(version, '.', 1, 3) + 1)) DESC) AS RANG
FROM TstTab T)
SELECT Fld1, Fld2, Version FROM T1 WHERE RANG = 1

[081krieger]

Если да, то в Oracle 1-м запросом это можно сделать так (привет, 081krieger, будь проще ):

Согласен, что решение не самое оптимальное Но оно быстрее всех пришло в голову)
А вообще, изначально лучше бы хранить номер версии в числовом формате, а не в тексте.

[Hamely]

>> Кстати, поле версии в вашей таблице уникально?
само поле - нет, а для каждого продукта - уникальны (но это не проблема - JOINы IN MAX и т.д. всё реализовано, только вот надо конвертнуть версии...)

ух ребята, вы знаете толк в.... всем спасибо! буду пробовать.

[Архон]

, только вот надо конвертнуть версии...)

А да. Этот ваш INET_ATON корвертнет в 4-х байтовое число с добавл. ведущих нулей в каждом байте - точно повезло.

Можно было не мозг.

select *
from TestTab
where INET_ATON(version) = (select max(INET_ATON(version)) from TestTab)

Удачи.

[Hamely]

Спасибо всем Тему можно закрывать.