Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1450

need help with ordering groups of records

Show all headers | View raw

I have a list of customers and addresses they have visited, each record has a count of visits. I need to re-sort the list in such a way that customers with higher counts will be higher on the list, but all his/her records should stay together

To illustrate:
declare @T table ( ID int, NAME varchar(32), ADDR varchar(32), CNT int );
insert into @T values
(1001, 'JOHN DOE', '100 MAIN ST', 200),
(1001, 'JOHN DOE', '2040 1ST ST',  50),
(1001, 'JOHN DOE', '1 WESTIN ST', 220),
(1225, 'BOB GARE', '50 GREEN ST', 195),
(1405, 'JUAN SILVA', '50 GREEN ST', 225),
(1405, 'JUAN SILVA', '300 PALM DR',  25);

I need to get list ordered like below:
ID	NAME	ADDR	CNT
1405	JUAN SILVA	50 GREEN ST	225
1405	JUAN SILVA	300 PALM DR	25
1001	JOHN DOE	100 MAIN ST	200
1001	JOHN DOE	2040 1ST ST	50
1001	JOHN DOE	1 WESTIN ST	220
1225	BOB GARE	50 GREEN ST	195

As you see, Juan Silva has 225 visits, which is highest of them all, so all his records are sorted before everybody else. The order of records within group belonging to the same customer is not important.

Any hint or idea would be appreciated.

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

need help with ordering groups of records migurus <migurus@yahoo.com> - 2013-04-17 18:21 -0700
  Re: need help with ordering groups of records rja.carnegie@gmail.com - 2013-04-17 19:56 -0700
    Re: need help with ordering groups of records migurus <migurus@yahoo.com> - 2013-04-30 10:14 -0700
  Re: need help with ordering groups of records Gints Plivna <gints.plivna@gmail.com> - 2013-04-23 05:19 -0700
    Re: need help with ordering groups of records migurus <migurus@yahoo.com> - 2013-04-30 10:13 -0700
    Re: need help with ordering groups of records rja.carnegie@gmail.com - 2013-04-30 11:37 -0700

csiph-web