X-Received: by 10.224.160.65 with SMTP id m1mr6735835qax.2.1366253767392; Wed, 17 Apr 2013 19:56:07 -0700 (PDT) X-Received: by 10.49.98.65 with SMTP id eg1mr955225qeb.2.1366253767377; Wed, 17 Apr 2013 19:56:07 -0700 (PDT) Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!news-out.readnews.com!transit4.readnews.com!border4.nntp.dca.giganews.com!border2.nntp.dca.giganews.com!nntp.giganews.com!ca1no1933900qab.0!news-out.google.com!ef9ni1990qab.0!nntp.google.com!ca1no1933881qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.ms-sqlserver Date: Wed, 17 Apr 2013 19:56:07 -0700 (PDT) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=92.41.243.121; posting-account=dELd-gkAAABehNzDMBP4sfQElk2tFztP NNTP-Posting-Host: 92.41.243.121 References: User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: Subject: Re: need help with ordering groups of records From: rja.carnegie@gmail.com Injection-Date: Thu, 18 Apr 2013 02:56:07 +0000 Content-Type: text/plain; charset=ISO-8859-1 Lines: 66 Xref: csiph.com comp.databases.ms-sqlserver:1451 On Thursday, 18 April 2013 02:21:02 UTC+1, migurus wrote: > 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. I take it it doesn't matter that Juan Silva has 25 visits at another address, and John Doe has 200, 50, and 220 visits at 3 addresses, which adds up to more than Juan Silva's 225 + 25. So - I'm not on the server just now, but, will this work? SELECT CUSTOMER.ID, CUSTOMER.NAME, CUSTOMER.ADDR, CUSTOMER.CNT FROM @T CUSTOMER JOIN ( SELECT ID, MAX(CNT) AS MAX_VISITS FROM @T GROUP BY ID ) MAX_VISITS ON ( CUSTOMER.ID = MAX_VISITS.ID ) ORDER BY MAX_VISITS.MAX_VISITS DESC , CUSTOMER.CNT DESC -- I think if you /do/ want to give John Doe a count of 470 visits -- for ranking, then you just need to use SUM(CNT) instead of MAX(CNT). -- And probably change MAX_VISITS to ALL_VISITS or something. -- -- And indexes... well, a non-unique index on ID, anyway. -- Or a unique index on (ID, ADDR), but that's bad. Is there -- an ADDR_ID column that you could pick up? And if you put CNT in -- there too, it looks crazy and it makes updates slower but -- I think it helps this query. Or, just create the table of -- MAX_VISITS. -- -- In recent editions of SQL Server, you can also pre-define sub-queries -- by starting a statement with the keyword "WITH"; confusingly, -- WITH is also used to introduce "hints", so, make sure you get -- the /right/ page in the manual. It's late here and I don't remember -- how it goes, sorry ;-)