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