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

Groups > comp.databases.ms-sqlserver > #1450 > unrolled thread

need help with ordering groups of records

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#1450 — need help with ordering groups of records

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.

Any hint or idea would be appreciated.

[toc] | [next] | [standalone]

#1451

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

[toc] | [prev] | [next] | [standalone]

#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

[toc] | [prev] | [next] | [standalone]

#1452

ceturtdiena, 2013. gada 18. aprīlis 04:21:02 UTC+3, migurus rakstīja:
> 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
> 
[skipped]
> 
> 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.
> 
> 
> 
> Any hint or idea would be appreciated.


select * from @t t
order by MAX(cnt) OVER (PARTITION BY NAME) desc, cnt desc

Gints Plivna
http://www.gplivna.eu

[toc] | [prev] | [next] | [standalone]

#1459

On Tuesday, April 23, 2013 5:19:15 AM UTC-7, Gints Plivna wrote:
> ceturtdiena, 2013. gada 18. aprīlis 04:21:02 UTC+3, migurus rakstīja:
> 
> > 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
> 
> > 
> 
> [skipped]
> 
> > 
> 
> > 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.
> 
> > 
> 
> > 
> 
> > 
> 
> > Any hint or idea would be appreciated.
> 
> 
> 
> 
> 
> select * from @t t
> 
> order by MAX(cnt) OVER (PARTITION BY NAME) desc, cnt desc
> 
> 
> 
> Gints Plivna
> 
> http://www.gplivna.eu

Thank you it works

[toc] | [prev] | [next] | [standalone]

#1461

On Tuesday, 23 April 2013 13:19:15 UTC+1, Gints Plivna  wrote:
> ceturtdiena, 2013. gada 18. aprīlis 04:21:02 UTC+3, migurus rakstīja:
> > Any hint or idea would be appreciated.
> 
> select * from @t t
> order by MAX(cnt) OVER (PARTITION BY NAME) desc, cnt desc

I can never remember how you do one of those!  ;-)

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web