X-Received: by 10.224.88.200 with SMTP id b8mr46868682qam.8.1367342078820; Tue, 30 Apr 2013 10:14:38 -0700 (PDT) X-Received: by 10.50.196.227 with SMTP id ip3mr972178igc.10.1367342077892; Tue, 30 Apr 2013 10:14:37 -0700 (PDT) Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!s14no1301754qam.0!news-out.google.com!ef9ni37315qab.0!nntp.google.com!s14no1301750qam.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.ms-sqlserver Date: Tue, 30 Apr 2013 10:14:37 -0700 (PDT) In-Reply-To: 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 References: User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: Subject: Re: need help with ordering groups of records From: migurus Injection-Date: Tue, 30 Apr 2013 17:14:38 +0000 Content-Type: text/plain; charset=ISO-8859-1 Xref: csiph.com comp.databases.ms-sqlserver:1460 On Wednesday, April 17, 2013 7:56:07 PM UTC-7, rja.ca...@gmail.com wrote: > 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 ;-) Thank you, your solution works