急！！帮忙写SQL语句

[hmilyzhen]

我想取出分组后满足每条分组记录的第一条记录
如有1个字段

通讯地址
192.168.1.111,800
192.168.1.111,801
192.168.1.250,801
192.168.1.250,802
192.168.1.250,803
192.168.1.251,805

我现在想按前半部分分组 然后取出每组分组后的第一条记录

既得到如下记录
通讯地址
192.168.1.111,800
192.168.1.250,801
192.168.1.251,805

有人能帮实现一下吗？写不出来呢 现在只能分组
select substring(通讯地址,1,13) from A group by substring(通讯地址,1,13)

[人生无悔]

--try
select substring(通讯地址,1,13),min(substring(通讯地址,15,3)) from A group by substring(通讯地址,1,13)

[hmilyzhen]

[Quote=引用 1 楼 的回复:]
SQL code


--try
select substring(通讯地址,1,13),min(substring(通讯地址,15,3)) from A group by substring(通讯地址,1,13)
[/Quote]


您这个我可以查出来 那我怎么查出这条记录的所有信息啊 因为它还有其它字段 后面用groupby 还没法把其它字段列出呢？

[叶子]

declare @t table([通讯地址] varchar(17))
insert @t
select '192.168.1.111,800' union all
select '192.168.1.111,801' union all
select '192.168.1.250,801' union all
select '192.168.1.250,802' union all
select '192.168.1.250,803' union all
select '192.168.1.251,805'

select * from @t t
where right([通讯地址],3)=
(select min(right([通讯地址],3)) from @t where left([通讯地址],13)=left(t.[通讯地址],13))
/*
通讯地址
-----------------
192.168.1.111,800
192.168.1.250,801
192.168.1.251,805
*/

--也可以用逗号做标记分割

[孤独加百列]

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
	DROP TABLE tba
END
GO
CREATE TABLE tba
(
	ID INT IDENTITY(1,1),
	IP VARCHAR(100)
)
GO
INSERT INTO tba
SELECT '192.168.1.111,800' UNION
SELECT '192.168.1.111,801'UNION
SELECT '192.168.1.250,801'UNION
SELECT '192.168.1.250,802'UNION
SELECT '192.168.1.250,803'UNION
SELECT '192.168.1.251,805'
GO
SELECT * FROM tba AS A WHERE IP = (SELECT TOP 1 IP FROM tba WHERE LEFT(IP,CHARINDEX(',',IP)) = LEFT(A.IP,CHARINDEX(',',A.IP)))

ID	IP
1	192.168.1.111,800
3	192.168.1.250,801
6	192.168.1.251,805

[hmilyzhen]

[Quote=引用 3 楼 的回复:]
SQL code


declare @t table([通讯地址] varchar(17))
insert @t
select '192.168.1.111,800' union all
select '192.168.1.111,801' union all
select '192.168.1.250,801' union all
select '192.168.1.250,8……
[/Quote]


正解！！ 问题解决了 谢谢各位的回复啦~