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

need help with ordering groups of records

X-Received	by 10.224.178.205 with SMTP id bn13mr6536051qab.3.1366248063075; Wed, 17 Apr 2013 18:21:03 -0700 (PDT)
X-Received	by 10.49.119.99 with SMTP id kt3mr917741qeb.22.1366248063034; Wed, 17 Apr 2013 18:21:03 -0700 (PDT)
Path	csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!ca1no1192609qab.0!news-out.google.com!ef9ni1990qab.0!nntp.google.com!ca1no1192607qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	comp.databases.ms-sqlserver
Date	Wed, 17 Apr 2013 18:21:02 -0700 (PDT)
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=216.14.58.122; posting-account=PG2dbQkAAADVILsQ0GhgAM6hZK18SIjs
NNTP-Posting-Host	216.14.58.122
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<c2fb326a-1dee-4d93-b41e-16e00868c506@googlegroups.com> (permalink)
Subject	need help with ordering groups of records
From	migurus <migurus@yahoo.com>
Injection-Date	Thu, 18 Apr 2013 01:21:03 +0000
Content-Type	text/plain; charset=ISO-8859-1
Xref	csiph.com comp.databases.ms-sqlserver:1450

Show key headers only | 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