Bubblesort in T-SQL - 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 2005 Forums

Transact-SQL (2005)

Bubblesort in T-SQL

Author

Topic

Thrall
Starting Member

4 Posts

Posted - 2009-04-20 : 05:26:52

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

30421 Posts

Posted - 2009-04-20 : 05:31:49

Bubblesort in T-SQL?

SELECT * FROM Table1 ORDER BY Col1, Col2

E 12°55'05.63"
N 56°04'39.26"

Thrall
Starting Member

4 Posts

Posted - 2009-04-20 : 06:27:06

:) 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...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-20 : 06:38:47

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++.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-20 : 06:39:36

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

Thrall
Starting Member

4 Posts

Posted - 2009-04-20 : 06:55:18

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.

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-20 : 07:39:30

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).

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-20 : 08:10:49

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

Thrall
Starting Member

4 Posts

Posted - 2009-04-20 : 09:26:39

OMG!! This looks serious! Thanks a lot TransactCharlie!! sorry for making you nearly vomiting....

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-20 : 10:00:09

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

waynefitzy
Starting Member

1 Post

Posted - 2010-11-08 : 04:04:13

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"

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-11-08 : 04:12:56

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.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-08 : 04:13:45

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources