Complicated sorting in one sql

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)

Complicated sorting in one sql

Author

Topic

abugov

6 Posts

Posted - 2008-07-03 : 04:23:29

Hello,

I am trying to write a query that returns this result set:

Table “Tests”:

Id Test
1 B
2 B
3 C
4 A
5 B

Result Set:
Id Test
1 B
2 B
5 B
3 C
4 A

The tests are grouped by name, but the order is by id.

I have come up with this:

SELECT Id, Test,
RANK() OVER(PARTITION BY Test ORDER BY Id) Rank1
FROM Tests

The result set groups the tests by name as needed, but the order is
by test, not by id...

any ides?

10x,
Alex

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-03 : 04:34:21

Is this what you want ?

DECLARE @Tests TABLE
(
	ID	int,
	Test	CHAR(1)
)

INSERT INTO @Tests
SELECT	1, 'B'	UNION ALL
SELECT	2, 'B'	UNION ALL
SELECT	3, 'C'	UNION ALL
SELECT	4, 'A'	UNION ALL
SELECT	5, 'B'

SELECT	t.ID, t.Test
FROM	@Tests t
	INNER JOIN	
	(
		SELECT	Test, cnt = COUNT(*)
		FROM	@Tests
		GROUP BY Test
	) c	ON	t.Test	= c.Test
ORDER BY c.cnt DESC, t.ID

/*
ID          Test 
----------- ---- 
          1 B
          2 B
          5 B
          3 C
          4 A

(5 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

abugov

6 Posts

Posted - 2008-07-03 : 04:43:00

10x for the quick reply.
this is not wat i am looking for, the order is not determine by
the count of test.

i.e.: if the "tests" table would be this (i've added on more row for 'A'):
1 B
2 B
3 C
4 A
5 A
6 B

the wanted result would be:
1 B
2 B
6 B
3 C
4 A
5 A

10x,
Alex

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-03 : 04:51:20

[code]SELECT t.id,t.Test
FROM YourTable t
INNER JOIN (SELECT Test,COUNT(*) as reccnt
FROM YourTable
GROUP BY Test)t1
ON t1.Test=t.Test
ORDER BY t1.reccnt DESC,t.id ASC[/code]

abugov

6 Posts

Posted - 2008-07-03 : 04:58:06

misled u with the data, Test 'A' can apear 3 times (the same count as 'B'),
so you can't use the count in order by

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-03 : 04:58:56

quote:
Originally posted by abugov

10x for the quick reply.
this is not wat i am looking for, the order is not determine by
the count of test.

Then what is the ordering that you want ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-03 : 05:00:53

This ?

DECLARE @Tests TABLE
(
	ID	int,
	Test	CHAR(1)
)

INSERT INTO @Tests
SELECT	1, 'B'	UNION ALL
SELECT	2, 'B'	UNION ALL
SELECT	3, 'C'	UNION ALL
SELECT	4, 'A'	UNION ALL
SELECT	5, 'A'	UNION ALL
SELECT	6, 'B'

SELECT	t.ID, t.Test
FROM	@Tests t
	INNER JOIN	
	(
		SELECT	Test, ID = MIN(ID)
		FROM	@Tests
		GROUP BY Test
	) c	ON	t.Test	= c.Test
ORDER BY c.ID, t.ID

/*
ID          Test 
----------- ---- 
          1 B
          2 B
          6 B
          3 C
          4 A
          5 A

(6 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-03 : 05:27:55

quote:
Originally posted by abugov

misled u with the data, Test 'A' can apear 3 times (the same count as 'B'),
so you can't use the count in order by

then specify wats your rule for ordering

abugov

6 Posts

Posted - 2008-07-03 : 06:04:00

Yes thats it! i think u did it.
I notice that the select in the inner join rely on the
insert order of the records (because u can't have an OREDER BY).
Do u think that i can rely on that?

thank u very much,
Alex

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-03 : 06:17:12

quote:
Originally posted by abugov

Yes thats it! i think u did it.
I notice that the select in the inner join rely on the
insert order of the records (because u can't have an OREDER BY).
Do u think that i can rely on that?

thank u very much,
Alex

didnt get that. you dont require ORDER BY in inner select as MIN always returns minimum value of field regardless of order of entry.

abugov

6 Posts

Posted - 2008-07-03 : 07:37:11

hmm ... u right :)
thank u very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources