CODEWITHSUNDEEP
sqlsql-server
chhenning
chhenning

Reputation: 2077

Sorting with sql

I have a question on how to sort data using sql. For that I made up a simple example to illustrate my problem.

if object_id('MyTable', 'U') is not null drop table  dbo.MyTable;

create table MyTable
(
    country varchar(10) not null
    , town varchar( 10 ) not null
    , amount int not null
)

insert into MyTable values
( 'US', 'NYC', 100 )
, ( 'US', 'BAL', 150 )
, ( 'US', 'WAS', 200 )
, ( 'CA', 'MON', 100 )
, ( 'CA', 'TOR', 150 )
, ( 'CA', 'VAN', 200 )

How can I sort the data in a sense the all "countries are sorted by the amount in descending order AND that the towns in alphabetical order for each country.

Thanks, Christian

Upvotes: 1

Views: 88

Answers (3)

Gulli Meel
Gulli Meel

Reputation: 891

I am not sure whether you want to order by total amount of a country or just amount . for total amount of country use below.

select 
mt.country,
ctotal.countrycotal,
mt.town,
mt.amount
from 
(
SELECT 
 country, 
 SUM(amount) as countrycotal, 
 FROM        MyTable 
 group BY  country

) ctotal inner join Mytable mt on ctotal.country = mt.country

order by ctotal.countrytotal,ctotal.country,mt.town

for just amount use below

select * from Mytable 
order by mt.amount,mt.country,mt.town

Upvotes: 0

RBarryYoung
RBarryYoung

Reputation: 56725

I think that this should do it:

SELECT
    country,
    SUM(amount) OVER(PARTITION BY country) as CountryTotal,
    town,
    amount
FROM        MyTable
ORDER BY    CountryTotal, country, town

Upvotes: 1

Erica
Erica

Reputation: 470

To sort in SQL, use Order By: http://msdn.microsoft.com/en-us/library/ms188385.aspx

So if you wanted sorted by Country, then Amount, then Town, you'd add an Order By clause after your Where class like:

ORDER BY Country, Amount DESC, Town

Upvotes: 3

Related Questions