简单问题 高手指教
有如下表
id   group   name
1       2           ss
2       3           ds
3       3           er
4       2           sd
5       3           sd
我想得到同一个组的上一个id号，id是递增的
比如我知道   id   5   我怎么得到他的上一个id3呢？

------解决方案--------------------
select top 1 id from 表 where [group]=(select [group] from 表 where id=5) and id < 5 order by id desc

------解决方案--------------------
select
t.*
from
表 t
where
t.id <5
and
not exists(select 1 from 表 where and id <5 and group=t.group and id> t.id)
------解决方案--------------------
--测试数据：@Test
declare @Test table(id int,[group] int,name varchar(2))
insert @Test
select 1,2, 'ss ' union all
select 2,3, 'ds ' union all
select 3,3, 'er ' union all
select 4,2, 'sd ' union all
select 5,3, 'sd '

declare @id int
set @id=5

select top 1 * from @Test a where [group]=(select [group] from @Test where id=@id) and id <@id order by id desc

------解决方案--------------------
create table tb (id int,[group] int,name varchar(10))
insert into tb values(1, 2, 'ss ')
insert into tb values(2, 3, 'ds ')
insert into tb values(3, 3, 'er ')
insert into tb values(4, 2, 'sd ')
insert into tb values(5, 3, 'sd ')
go
declare @id as int
set @id = 5

select max(id) id from
(select name , max(id) id from tb group by name) t
where id <> @id

drop table tb

/*
id
-----------
3

（所影响的行数为 1 行）
*/
------解决方案--------------------

declare @a table(id int identity(1,1),groups int,name varchar(20))
insert @a
select 2, 'ss '
union all
select 3, 'ds '
union all
select 3, 'er '
union all
select 2, 'sd '
union all
select 3, 'sd '

select top 1 * from @a where groups=(select groups from @a where id=5 ) order by id desc

/*

（所影响的行数为 5 行）

id groups name
----------- ----------- --------------------
5 3 sd

（所影响的行数为 1 行）

*/
------解决方案--------------------
都是高手，学习
------解决方案--------------------
少了个条件 id <5
------解决方案--------------------
declare @a table(id int identity(1,1),groups int,name varchar(20))
insert @a
select 2, 'ss '
union all
select 3, 'ds '
union all
select 3, 'er '
union all
select 2, 'sd '
union all
select 3, 'sd '

select top 1 * from @a where groups=(select groups from @a where id=5 ) and id <5 order by id desc


/*

（所影响的行数为 5 行）

id groups name
----------- ----------- --------------------