Order by 2 columns at the same time

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)

Order by 2 columns at the same time

Author

Topic

rekiller
Starting Member

31 Posts

Posted - 2007-09-13 : 16:01:10

Hi, i have a problem
I need to order some special data. Data must order like a quee.
Example:
I have 6 rows, with 2 columns, and Integer data

COLUMN A COLUMN B
423 562
748 562
321 562
503 562
700 562
700 460
748 641
700 641

Theses data is order by Column B

If i order by Column A i got the following, but colum A is not order

423 562
748 562
748 641
321 562
503 562
700 460
700 562
700 641

But columB now is not order

I Want This

COLUMN A COLUMN B
423 562
748 562
321 562
503 562
700 562
700 460
700 641 --here is the problem solved
748 641

How can i deal with this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-13 : 16:03:04

OrRDER BY Column1, Column2

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rekiller
Starting Member

31 Posts

Posted - 2007-09-13 : 16:09:42

Nop,
I want this
423 562
748 562
321 562
503 562
700 562
700 460
700 641 --here is the problem solved
748 641

Note that the last row 748 will be in 3rd position if i order by columA , columnB.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-13 : 16:11:03

quote:
Originally posted by tkizer

OrRDER BY Column1, Column2

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

This won't work for what he is asking, my feeling is it will require a table variable unless there is an easier way ( I am sure there is).

Future guru in the making.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-13 : 16:16:46

Is this what you want?

DECLARE @T TABLE (A INT, B INT)

INSERT @T
SELECT 423, 562
UNION ALL SELECT 748, 562
UNION ALL SELECT 321, 562
UNION ALL SELECT 503, 562
UNION ALL SELECT 700, 562
UNION ALL SELECT 700, 460
UNION ALL SELECT 748, 641
UNION ALL SELECT 700, 641


SELECT *
FROM @T
GROUP BY
	A, B
ORDER BY 
	A, B

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-13 : 16:19:08

quote:

Originally posted by Lamprey

Is this what you want?

DECLARE @T TABLE (A INT, B INT)

INSERT @T
SELECT 423, 562
UNION ALL SELECT 748, 562
UNION ALL SELECT 321, 562
UNION ALL SELECT 503, 562
UNION ALL SELECT 700, 562
UNION ALL SELECT 700, 460
UNION ALL SELECT 748, 641
UNION ALL SELECT 700, 641


SELECT *
FROM @T
GROUP BY
	A, B
ORDER BY 
	A, B

Still doesn't order both columns though.

Future guru in the making.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 16:24:39

Kaping!

-- Prepare sample data
DECLARE	@Sample TABLE (ColA INT, ColB INT)

INSERT	@Sample
SELECT	423, 562 UNION ALL
SELECT	748, 562 UNION ALL
SELECT	321, 562 UNION ALL
SELECT	503, 562 UNION ALL
SELECT	700, 562 UNION ALL
SELECT	700, 460 UNION ALL
SELECT	748, 641 UNION ALL
SELECT	700, 641

-- Show the expected output
SELECT		ColA,
		ColB
FROM		(
			SELECT		ColA,
					ColB,
					rA,
					rB,
					CASE
						WHEN rA > rB THEN rA
						ELSE rB
					END AS rZ
			FROM		(
						SELECT		ColA,
								ColB,
								COUNT(*) OVER (PARTITION BY ColA) AS rA,
								COUNT(*) OVER (PARTITION BY ColB) AS rB
						FROM		@Sample
					) AS e
		) AS k
ORDER BY	rZ DESC,
		CASE
			WHEN rZ = rA THEN rA
			ELSE rB
		END

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 16:27:58

Or the other way around

-- Show the expected output
SELECT		ColA,
		ColB,
		rA, rB, rZ
FROM		(
			SELECT		ColA,
					ColB,
					rA,
					rB,
					CASE
						WHEN rA > rB THEN rA
						ELSE rB
					END AS rZ
			FROM		(
						SELECT		ColA,
								ColB,
								COUNT(*) OVER (PARTITION BY ColA) AS rA,
								COUNT(*) OVER (PARTITION BY ColB) AS rB
						FROM		@Sample
					) AS e
		) AS k
ORDER BY	rZ,
		CASE
			WHEN rZ = rA THEN rB
			ELSE rA
		END DESC

E 12°55'05.25"
N 56°04'39.16"

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-13 : 16:30:02

quote:

Originally posted by Peso

Or the other way around

-- Show the expected output
SELECT		ColA,
		ColB,
		rA, rB, rZ
FROM		(
			SELECT		ColA,
					ColB,
					rA,
					rB,
					CASE
						WHEN rA > rB THEN rA
						ELSE rB
					END AS rZ
			FROM		(
						SELECT		ColA,
								ColB,
								COUNT(*) OVER (PARTITION BY ColA) AS rA,
								COUNT(*) OVER (PARTITION BY ColB) AS rB
						FROM		@Sample
					) AS e
		) AS k
ORDER BY	rZ,
		CASE
			WHEN rZ = rA THEN rB
			ELSE rA
		END DESC

E 12°55'05.25"
N 56°04'39.16"

You are one slick dude, but what the heck is kaping? Is that like badabing?

Future guru in the making.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-13 : 16:35:46

By the way, I just learned about 3 new things in your solution Peso, I have to go study them now!

Future guru in the making.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 16:37:07

Read Calvin & Hobbes

E 12°55'05.25"
N 56°04'39.16"

rekiller
Starting Member

31 Posts

Posted - 2007-09-13 : 17:10:01

:)

That is exactly what i was looking for.
Thanks.

I knew that it was complex and not only a columA and B order, he he.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 17:17:36

Not that complex. Just an descending order by most occuring item and ascending by the other column in the record pair.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 17:46:48

A small alteration for appearance.

-- Prepare sample data
DECLARE	@Sample TABLE (ColA INT, ColB INT)

INSERT	@Sample
SELECT	423, 562 UNION ALL
SELECT	748, 562 UNION ALL
SELECT	321, 562 UNION ALL
SELECT	503, 562 UNION ALL
SELECT	700, 562 UNION ALL
SELECT	700, 460 UNION ALL
SELECT	748, 641 UNION ALL
SELECT	700, 641

-- Show the expected output
SELECT		ColA,
		ColB
FROM		(
			SELECT		ColA,
					ColB,
					rA,
					rB,
					CASE
						WHEN rA > rB THEN rA
						ELSE rB
					END AS rZ
			FROM		(
						SELECT		ColA,
								ColB,
								COUNT(*) OVER (PARTITION BY ColA) AS rA,
								COUNT(*) OVER (PARTITION BY ColB) AS rB
						FROM		@Sample
					) AS e
		) AS k
ORDER BY	rZ DESC,
		CASE
			WHEN rZ = rA THEN rB
			ELSE rA
		END

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-13 : 18:17:25

quote:
Originally posted by Peso

Not that complex. Just an descending order by most occuring item and ascending by the other column in the record pair.

E 12°55'05.25"
N 56°04'39.16"

Is that what the OP wanted? I thought I was having a brain issue after looking at the oriiginal requirement to sort on two columns. :)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-09-13 : 18:21:46

This only thing I don't understand is the requirement. What is it?

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 18:26:35

Some kind of "rolling sequence".

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-09-13 : 18:30:50

OK. I can't imagine the reason behind that requirement. I don't think I want to either.

CODO ERGO SUM

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-13 : 18:34:08

quote:
Originally posted by Michael Valentine Jones

OK. I can't imagine the reason behind that requirement. I don't think I want to either.

CODO ERGO SUM

That was exactly my thought when I first read it, though it was an interesting problem, at least for me.

Future guru in the making.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 18:39:38

OP mentioned a queue, so I think there is a reason for this.

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-13 : 19:03:30

A two column weighted queue? *shrug*

Next Page