DECLARE @cust_code varchar(10)
IF object_id('tempdb..#MyTempTable') IS NOT NULL
BEGIN
DROP TABLE #MyTempTable
END
CREATE TABLE #MyTempTable
(
cust_code varchar(10) null,
Member_Code varchar(10) null,
No int null,
Jobs varchar(50) null
)
DECLARE cust_CURSOR CURSOR FOR select cust_code
from Table_1
where 1=1 group by cust_code order by cust_code
OPEN cust_CURSOR;
FETCH NEXT FROM cust_CURSOR
INTO @cust_code;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #MyTempTable select top (1)* from Table_1 where cust_code = @cust_code order by No desc
FETCH NEXT FROM cust_CURSOR INTO @cust_code;
END;
CLOSE cust_CURSOR;
deallocate cust_CURSOR;
select * from #MyTempTable
หรือถ้าจะเขียน เป็น Store Proc ไว้ใช้ง่ายๆ ก็แบบนี้เลยครับ ตอนใช้เราก็แค่ EXEC SPSelctData อย่างเดียวครับ
Code
IF OBJECT_ID('SPSelctData') IS NOT NULL DROP PROC SPSelctData
GO
CREATE PROC SPSelctData
AS
DECLARE @cust_code varchar(10)
IF object_id('tempdb..#MyTempTable') IS NOT NULL
BEGIN
DROP TABLE #MyTempTable
END
CREATE TABLE #MyTempTable
(
cust_code varchar(10) null,
Member_Code varchar(10) null,
No int null,
Jobs varchar(50) null
)
DECLARE cust_CURSOR CURSOR FOR select cust_code
from Table_1
where 1=1 group by cust_code order by cust_code
OPEN cust_CURSOR;
FETCH NEXT FROM cust_CURSOR
INTO @cust_code;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #MyTempTable select top (1)* from Table_1 where cust_code = @cust_code order by No desc
FETCH NEXT FROM cust_CURSOR INTO @cust_code;
END;
CLOSE cust_CURSOR;
deallocate cust_CURSOR;
select * from #MyTempTable
Load balance : Server 04
