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

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

Re: need help with ordering groups of records

Show all headers | View raw

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

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