Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Re: need help with ordering groups of records

Show all headers | View raw

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 ;-)

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | 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