Диапазоны адресов IPv6 [закрываются]
используйте row_number()
select * from
(
select *,
row_number() over(partition by cust_id order by Amount desc) rn
from tabl
) t where rn=1
или используйте подзапрос Corelate
select t1.* from table t1
where t1.Amount= (select max(Amount) from table t2
where t1.cust_id=t2.cust_id)
2 ответа
It is not correct to use IP addresses (neither IPv4, nor IPv6) in ranges. The correct way to group a particular "range" of IP addresses is using prefixes (CIDR notation) or masks (obsolete, only valid for IPv4, and insanity ensues if you try to use a non-contiguous mask).
Sometimes you will see someone (sometimes even applications, home routers, etc) using IPv4 ranges, but that is just the wrong way to do it.
Using Classless Inter-Domain Routing (CIDR) you will have a tuple
So, for example, an IPv6 "range" of 2620:0:860:2::/64 (wikimedia.org) represents all hosts from 2620:0:860:2:: up to 2620:0:860:2:FFFF:FFFF:FFFF:FFFF.
You shouldn't use two "bigint"s to store such a value in a database, but use any native representation in a single column, unless you want to make your developer life a nightmare. If your DBMS doesn't support integers this big, besides replacing your DBMS, I suggest using a fixed-size binary data column, 16 bytes long.
Использование СУБД с надлежащей поддержкой адресов IPv6 было бы неплохо идея. Вот пример с PostgreSQL, версия 8.3:
mydb=> CREATE TABLE Networks (name TEXT, prefix INET);
CREATE TABLE
mydb=> INSERT INTO Networks VALUES ('Documentation', '2001:DB8::/32');
INSERT 0 1
mydb=> INSERT INTO Networks VALUES ('ULA', 'FC00::/7');
INSERT 0 1
mydb=> INSERT INTO Networks VALUES ('Orchid', '2001:10::/28');
INSERT 0 1
mydb=> SELECT * FROM Networks;
name | prefix
---------------+---------------
Documentation | 2001:db8::/32
ULA | fc00::/7
Orchid | 2001:10::/28
(3 rows)
mydb=> SELECT * FROM Networks WHERE '2001:DB8::dcaf:BAD' << prefix;
name | prefix
---------------+---------------
Documentation | 2001:db8::/32
(1 row)