SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Bubblesort in T-SQL
 New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Thrall
Starting Member

4 Posts

Posted - 04/20/2009 :  05:26:52  Show Profile
Hi there,

Does anyone happen to have t-sql script/sproc being able to do bubblesorting ?

I'm trying to do it myself but unfortunately I didn't do much progress so any help will be more that appreciated.

Many thanks in advance!

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 04/20/2009 :  05:31:49  Show Profile  Visit SwePeso's Homepage
Bubblesort in T-SQL?

SELECT * FROM Table1 ORDER BY Col1, Col2



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Thrall
Starting Member

4 Posts

Posted - 04/20/2009 :  06:27:06  Show Profile
:) Thanks for that. I know using built-in functions is the best thing to do :)

However I need this script to prepare for the exam where we can expect such question. And unfortunately I'm stuck with it...
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 04/20/2009 :  06:38:47  Show Profile  Visit robvolk's Homepage
Doing bubble sorts in T-SQL? Your professors are idiots. You're better off posting Peso's code and telling them it's the right way to sort in SQL. Bubble sorts belong in C/C++.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 04/20/2009 :  06:39:36  Show Profile  Visit Transact Charlie's Homepage
That doesn't sound like a good exam question if they actually expect you to write a sorting algorithm in t-sql. Totally defeats the purpose of using the database engine in the first place.

can you post an example question? I'm sure there's more to it than your initial request.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Thrall
Starting Member

4 Posts

Posted - 04/20/2009 :  06:55:18  Show Profile
Example question would sound sth like that:

We have 6 values in the table bubblesort and we want to sort them using bubblesort method. Please create a procedure which will allow us to do so.
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/20/2009 :  07:39:30  Show Profile
Using SQL to bubblesort. Wow!

You sure the question doesn't mean create a procedure in another language like C++?? Table could be a vague term here, it could actually mean a data structure (like a datatable).

Edited by - whitefang on 04/20/2009 07:47:22
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 04/20/2009 :  08:10:49  Show Profile  Visit Transact Charlie's Homepage
Wow. That's an awful question.

Which school and course are you doing?

jargon:
http://people.kldp.org/~eunjea/jargon/index.php?query=+bubble&keyword=on

code:

IF OBJECT_ID('tempdb..#foo') IS NOT NULL DROP TABLE #foo
IF OBJECT_ID('bubbleSort') IS NOT NULL DROP PROCEDURE bubbleSort
GO

CREATE TABLE #foo (
	[position] INT IDENTITY (1,1)
	, [value] INT
	)
GO

-- Populate the table
INSERT #foo SELECT CAST(CAST(NEWID() AS VARBINARY) AS INT)
GO 50

-- Make some gaps in the identity column
DELETE f FROM #foo f JOIN (SELECT TOP 10 [position] FROM #foo ORDER BY NEWID()) a ON a.[position] = f.[position]

-- Show #foo ordered by identity
SELECT * FROM #foo ORDER BY [position] ASC

-- Show #foo ordered by value (this should be the same as the bubble sort)
SELECT * FROM #foo ORDER BY [value] ASC
GO


-- Procedure to sort
CREATE PROCEDURE bubbleSort AS BEGIN

	SET NOCOUNT ON
	CREATE TABLE #t (
		[order] INT
		, [value] INT
		)

	DECLARE
		@swapped BIT
		, @counter INT
		, @topVal INT

	-- Populate #t
	INSERT INTO #t (
		[order]
		, [value]
		)
	SELECT
		ROW_NUMBER() OVER(ORDER BY [position] ASC)
		, [value]
	FROM
		#foo

	SELECT
		@swapped = 1
		, @counter = 1
		, @topVal = COUNT([order])
	FROM
		#t

	IF ( ISNULL(@topVal, -1) < 2 ) BEGIN
		PRINT 'No data'
		SELECT * FROM #t
		RETURN
	END

	WHILE (@swapped = 1) BEGIN

		SET @counter = 1
		SET @swapped = 0

		WHILE (@counter < @topVal) BEGIN
		
			IF ((SELECT [value] FROM #t WHERE [order] = @counter) > (SELECT [value] FROM #t WHERE [order] = @counter + 1)) BEGIN
				UPDATE #t SET [order] = -1 WHERE [order] = @counter + 1
				UPDATE #t SET [order] = @counter + 1 WHERE [order] = @counter
				UPDATE #t SET [order] = @counter WHERE [order] = -1
				SET @swapped = 1
			END
		
			SET @counter = @counter + 1
		END
	END

	SELECT * FROM #t ORDER BY [order]
END
GO

-- Use the bubble sort
EXEC bubbleSort


Writing that nearly made me vomit.




Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Thrall
Starting Member

4 Posts

Posted - 04/20/2009 :  09:26:39  Show Profile
OMG!! This looks serious! Thanks a lot TransactCharlie!! sorry for making you nearly vomiting....
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 04/20/2009 :  10:00:09  Show Profile  Visit Transact Charlie's Homepage
Wow -- cool those heels.

As whitefang pointed out:

Are you 100% sure that you are required to write a TSQL version of bubble sort? My horrible procedure should illustrate just how impractical and generally *wrong* that is.

Ran through query analyser and:

SELECT * FROM #foo ORDER BY [value]

is about a million times faster (not kidding -- seriously)



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

waynefitzy
Starting Member

United Kingdom
1 Posts

Posted - 11/08/2010 :  04:04:13  Show Profile
i don't normally contribute to forums but raeding this has somewhat insensed me.
STOP BLOODY WHINGING
There is only one of you who came up with an answer, the rest of you haven't got a clue.
the author asked a question, and what he/she got back was junk, if you can't do it dont make excuses. don't say "are you sure that's what is being asked", "your professors are idiots", "you have to do it in another language"
Stick to the old idiom "if you are incapable of answereing the question, keep your mouth (keyboard in this case) shut"
Go to Top of Page

michael.appleton
Posting Yak Master

United Kingdom
160 Posts

Posted - 11/08/2010 :  04:12:56  Show Profile  Click to see michael.appleton's MSN Messenger address
Surely it's more important to find out if the question being asked is valid than just answering it blindly. I would much rather know what bad practice is than how to do something in a bad way without realising it was rubbish.

Edited by - michael.appleton on 11/08/2010 04:27:04
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 11/08/2010 :  04:13:45  Show Profile  Visit webfred's Homepage
They are not INCAPABLE.
It must be allowed to state that searching for a solution like this is wrong.


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000