quote:

---

Originally posted by ms65g

SET NOCOUNT ON
--Befor:
SELECT * FROM #temp
/*
ID          Code
----------- ----
1           AAA
4           DDD
7           GGG
9           III
2           BBB
5           EEE
3           CCC
8           HHH
6           FFF
*/
DECLARE @t TABLE (ID int, Code VARCHAR(3));

INSERT INTO @t
SELECT TOP 5 ID, Code
FROM #temp
ORDER BY Code ASC;

INSERT INTO @t
SELECT *
FROM #temp
WHERE ID NOT IN
      (SELECT TOP 5 ID
       FROM #temp
       ORDER BY Code ASC);

--After:       
SELECT * FROM @t
/*
ID          Code
----------- ----
1           AAA
2           BBB
3           CCC
4           DDD
5           EEE
7           GGG
9           III
8           HHH
6           FFF
*/

---

select ID,code from 
(
SELECT row_number() over (order by (select 0)) as sno,* FROM #temp
) as t
order by case when sno<=5 then 0 else 1 end,ID

quote:

---

Originally posted by ms65g

>>
OP wants to sort only the top 5 records. Rest of them should be in the same order
<<

Please re-read my results! Only TOP 5 record is sorted. and the rest is not any order.

---

1 AAA
2 BBB
4 DDD
7 GGG
9 III
5 EEE
3 CCC
8 HHH
6 FFF

SET NOCOUNT ON
--Befor:
SELECT * FROM #temp

DECLARE @t1 TABLE (ID int, Code VARCHAR(3));
DECLARE @t2 TABLE (ID int, Code VARCHAR(3));

INSERT INTO @t1
SELECT TOP 5 ID, Code
FROM #temp

INSERT INTO @t2
SELECT * FROM @t1 ORDER BY ID ASC

INSERT INTO @t2
SELECT *
FROM #temp
WHERE ID NOT IN
      (SELECT ID FROM @t1)

--After:       
SELECT * FROM @t2
/*
ID          Code
----------- ----
1           AAA
2           BBB
4           DDD
7           GGG
9           III
5           EEE
3           CCC
8           HHH
6           FFF
*/

quote:

---

Originally posted by ms65g

It must be okay:

SET NOCOUNT ON
--Befor:
SELECT * FROM #temp

DECLARE @t1 TABLE (ID int, Code VARCHAR(3));
DECLARE @t2 TABLE (ID int, Code VARCHAR(3));

INSERT INTO @t1
SELECT TOP 5 ID, Code
FROM #temp

INSERT INTO @t2
SELECT * FROM @t1 ORDER BY ID ASC

INSERT INTO @t2
SELECT *
FROM #temp
WHERE ID NOT IN
      (SELECT ID FROM @t1)

--After:       
SELECT * FROM @t2
/*
ID          Code
----------- ----
1           AAA
2           BBB
4           DDD
7           GGG
9           III
5           EEE
3           CCC
8           HHH
6           FFF
*/

---

select ID,code from 
(
SELECT row_number() over (order by (select 0)) as sno,* FROM #temp
) as t
order by case when sno<=5 then 0 else 1 end,ID

quote:

---

Originally posted by Transact Charlie

Hi madhivanan.

What does the segment highlighted in red from your code do?

select ID,code from 
(
SELECT row_number() over (order by (select 0)) as sno,* FROM #temp
) as t
order by case when sno<=5 then 0 else 1 end,ID

I assumed that from the OP's INSERT x SELECT y UNION ALL SELECT z.... approach there wouldn't be a guaranteed order in the way that he was expecting ( the first line of his SELECT construct first, then the next, etc, etc).

How does this pull out the required output?


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---

DROP TABLE #temp
GO
CREATE TABLE #temp ( ID int PRIMARY KEY, Code VARCHAR(3) )
GO
INSERT INTO #temp 
SELECT 1, 'AAA'
UNION ALL SELECT 4, 'DDD'
UNION ALL SELECT 7, 'GGG'
UNION ALL SELECT 9, 'III'
UNION ALL SELECT 2, 'BBB'
UNION ALL SELECT 5, 'EEE'
UNION ALL SELECT 3, 'CCC'
UNION ALL SELECT 8, 'HHH'
UNION ALL SELECT 6, 'FFF'
GO
SELECT * FROM #temp
GO
select ID,code from 
(
SELECT row_number() over (order by (select 0)) as sno,* FROM #temp
) as t
order by case when sno<=5 then 0 else 1 end,ID

quote:

---

Originally posted by LoztInSpace

Webfred has it right. As there are no guarantees to unspecified sort order, the OP's requested output can never actually be achieved reliably no matter what tricks you use.
The requirement is wrong - it implies there is a 'correct' answer for the rest of the rows and there can never be.
You might as well just order by code all the way through and dispense with all the magic voodoo that will probably not actually work.

---