sql query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sql query

Author

Topic

arulss
Starting Member

4 Posts

Posted - 2007-04-04 : 06:07:49

I am having a table like this

ID
1
1
1
2
2
2

I want the result table like

ID
1
2
1
2
1
2

How to write the query?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 06:10:34

Is this the ONLY column in the table?

Peter Larsson
Helsingborg, Sweden

arulss
Starting Member

4 Posts

Posted - 2007-04-04 : 06:12:31

No i have some other columns also but the order should be like this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 06:14:22

[code]-- Prepare sample data
DECLARE @Sample TABLE (ID INT)

INSERT @Sample
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 2

-- Stage the data
DECLARE @Stage TABLE (RecID INT IDENTITY(0, 1), ID INT)

INSERT @Stage
SELECT ID
FROM @Sample
ORDER BY ID

-- Show the expected output
SELECT ID
FROM @Stage
ORDER BY RecID % 3,
ID[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 06:19:22

In the staging part, you should add an extra row for ORDER BY, to ensure that all 1's and all 2's are sorted correct internally.

Peter Larsson
Helsingborg, Sweden

arulss
Starting Member

4 Posts

Posted - 2007-04-04 : 06:20:26

Could u please explain it.
Since the table which i have mentioned is a temp table written in a stored procedure.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-04 : 06:45:20

create your temp table with an identity column as what Peter has shown above.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-04 : 09:14:46

quote:
Originally posted by arulss

I am having a table like this

ID
1
1
1
2
2
2

I want the result table like

ID
1
2
1
2
1
2

How to write the query?

Can you give me practical example on when you are required to do this type of sorting?

Madhivanan

Failing to plan is Planning to fail

arulss
Starting Member

4 Posts

Posted - 2007-04-05 : 01:41:42

In the stored procedure i have created a temp table and stored some values from another table. I have to display the records which is in the temp table in the above order. It contains a column name called RecordOrder.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources