need help with ordering groups of records

[migurus]

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.

[rja.ca...@gmail.com]

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

[Gints Plivna]

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

[migurus]

Thank you it works

[migurus]

Thank you, your solution works

[rja.ca...@gmail.com]

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