Re: sort
From: Steve Kass (skass_at_drew.edu)
Date: 03/08/04
- Next message: Joe Celko: "Re: caculated field concept**"
- Previous message: DRM: "MySQL UPDATE to equivalent TSQL statement"
- In reply to: Itzik Ben-Gan: "Re: sort"
- Next in thread: Itzik Ben-Gan: "Re: sort"
- Reply: Itzik Ben-Gan: "Re: sort"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 07 Mar 2004 19:06:10 -0500
Cute! You can also order by a function returning the ip as a bigint:
create function ipVal (
@ipAdd varchar(15)
) returns bigint as begin
declare @a varchar(3), @b varchar(3), @c varchar(3), @d varchar(3)
set @ipAdd = replace(@ipAdd,'.',space(1))
exec master..xp_sscanf @ipAdd, '%s%s%s%s', @a output, @b output, @c
output, @d output
return 256.*(256.*(256.*@a + @b)+ @c) + @d
end
SK
Itzik Ben-Gan wrote:
>Or...
>
>CREATE VIEW Patterns
>AS
>SELECT REPLICATE('_',N1.n) + '.'
> + REPLICATE('_',N2.n) + '.'
> + REPLICATE('_',N3.n) + '.'
> + REPLICATE('_',N4.n) AS pattern,
> 1 AS s1, N1.n AS l1,
> N1.n+2 AS s2, N2.n AS l2,
> N1.n+N2.n+3 AS s3, N3.n AS l3,
> N1.n+N2.n+N3.n+4 AS s4, N4.n AS l4
>FROM Nums AS N1, Nums AS N2, Nums AS N3, Nums AS N4
>WHERE N1.n <= 3 AND N2.n <= 3 AND N3.n <= 3 AND N4.n <= 3
>GO
>
>SELECT T.*
>FROM T JOIN Patterns
> ON ipAdd LIKE pattern
>ORDER BY
> CAST(SUBSTRING(ipAdd,s1,l1) AS TINYINT),
> CAST(SUBSTRING(ipAdd,s2,l2) AS TINYINT),
> CAST(SUBSTRING(ipAdd,s3,l3) AS TINYINT),
> CAST(SUBSTRING(ipAdd,s4,l4) AS TINYINT)
>
>;-)
>
>
- Next message: Joe Celko: "Re: caculated field concept**"
- Previous message: DRM: "MySQL UPDATE to equivalent TSQL statement"
- In reply to: Itzik Ben-Gan: "Re: sort"
- Next in thread: Itzik Ben-Gan: "Re: sort"
- Reply: Itzik Ben-Gan: "Re: sort"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
