From: migurus@yahoo.com   
      
   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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)