Sort for the Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sort for the Data

Author

Topic

my_azka01
Starting Member

4 Posts

Posted - 2012-07-27 : 00:24:00

Please help,

I want to sort the database, example :
the data :
ARea Sales volume
Indonesia 100000000
Indonesia 90000000
INdonesia 1500000000
Indonesia 1
..... 34
Malaysia 600000000
Malaysia 300000000
Malaysia 3500
Malaysia 4500
..... 1

I want to show only 3 the best data every each Area, the other no need to show, just like
Area Sales Volume
Indonesia 1500000000
Indonesia 100000000
Indonesia 90000000
Malaysia 600000000
Malaysia 300000000
Malaysia 4500

Is there a query that can do this for me?
thx a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 00:45:14

[code]
SELECT Area,SalesVolume
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Area ORDER BY SalesVolume DESC) AS Seq,*
FROM Table
)t
WHERE Seq <=3
ORDER BY Area,Seq
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

my_azka01
Starting Member

4 Posts

Posted - 2012-07-27 : 04:49:49

how about the query with sqlserver2000, because it still not work

how about the colum_name which more than 2 column, how is the query?this the sample of my data
Mid Merchant Name MCC TRX AMT MDR Kode Cabang
00060100526 GLOBAL TELESHOP PT 4812 14 24,307,000 426,120 10107
00060100774 BATIK DANAR HADI PT 5631 162 120,465,962 3,011,656 0
00060100777 TOKO TATA LESTARI 5699 32 52,617,000 1,306,175 10602
00060100786 OXFORD JUNIOR 5697 15 77,200,000 1,447,600 10600
00060100957 TITO TAILOR 5697 12 28,900,000 361,000 10600
00060101028 BEATRIX 5992 9 3,890,000 97,250 10504
00060101060 RUMAH MAKAN GARUDA GROUP 5812 43 18,674,665 24,931 10503
00060101115 TOKO JAM SINAR KOTA 5944 26 21,160,000 380,130 10601
00060101118 YKC PERABOT 5712 4 8,499,000 212,475 10604
00060101120 AGROTEX UD 5193 10 13,241,875 317,805 10602
00060101173 TOKO MAHKOTA 5944 137 249,160,500 4,655,540 10500
00060101174 TOKO MAHKOTA 5944 13 9,885,000 304,720 10500
00060101187 TOKO MULTI ELEKTRONIK 5733 1 564,000 14,100 10606
00060101584 GLOBAL TELESHOP PT 4812 38 94,513,000 3,175,454 10107
00060101706 RESTU 5944 2 2,691,500 151,194 10613
00060101717 PT SUPER SETIA SAGITA 5411 287 63,402,400 1,204,645 0
00060101718 GAINS GALERY 5631 5 2,096,000 52,400 10605
00060101721 TIRTA VARIASI MOBIL UD 7538 34 213,062,500 4,261,250 10500
00060101769 TOKO BALI 5970 3 40,000,000 800,000 10602

I want to know the best of AMT/MDR/TRX for each [kode cabang], but in every [kode Cabang] the data show only for the TOP 3, the other no need to show

sorry, i still confuse

thx for your help

my_azka01
Starting Member

4 Posts

Posted - 2012-07-27 : 04:59:52

my review data :
Mid Merchant Name MCC TRX AMT MDR Kode Cabang
00060100526 GLOBAL TELESHOP PT 4812 14 24,307,000 426,120 10107
00060100774 BATIK DANAR HADI PT 5631 162 120,465,962 3,011,656 0
00060100777 TOKO TATA LESTARI 5699 32 52,617,000 1,306,175 10602
00060100786 OXFORD JUNIOR 5697 15 77,200,000 1,447,600 10600
00060100957 TITO TAILOR 5697 12 28,900,000 361,000 10600
00060101028 BEATRIX 5992 9 3,890,000 97,250 10504
00060101060 RUMAH MAKAN GARUDA GROUP 5812 43 18,674,665 24,931 10503
00060101115 TOKO JAM SINAR KOTA 5944 26 21,160,000 380,130 10601
00060101118 YKC PERABOT 5712 4 8,499,000 212,475 10604
00060101120 AGROTEX UD 5193 10 13,241,875 317,805 10602
00060101173 TOKO MAHKOTA 5944 137 249,160,500 4,655,540 10500
00060101174 TOKO MAHKOTA 5944 13 9,885,000 304,720 10500
00060101187 TOKO MULTI ELEKTRONIK 5733 1 564,000 14,100 10606
00060101584 GLOBAL TELESHOP PT 4812 38 94,513,000 3,175,454 10107
00060101706 RESTU 5944 2 2,691,500 151,194 10613
00060101717 PT SUPER SETIA SAGITA 5411 287 63,402,400 1,204,645 0
00060101718 GAINS GALERY 5631 5 2,096,000 52,400 10605
00060101721 TIRTA VARIASI MOBIL UD 7538 34 213,062,500 4,261,250 10500
00060101769 TOKO BALI 5970 3 40,000,000 800,000 10602

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 11:08:48

please explain us what you mean by best out of AMT/MDR/TRX ? do you mean highest value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

my_azka01
Starting Member

4 Posts

Posted - 2012-08-27 : 22:57:22

Yes, i mean the highest value base on AMT (top 10) per [Kode Cabang]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources