Início / dba / Perguntas / 223627
Accepted
kn3l
Asked: 2018-11-29 01:58:04 +0800 CST

Classifique por coluna específica mantendo outra coluna agrupada

  • 772
SELECT 
      CurrencyCodeFrom,
      CurrencyCodeTo, 
      MidRevalRate,
      MultiplierRate,
      RevalRate,
      CurrencyMarketCode,
      Added, 
      Modified, 
      UpdateReason
FROM 
     [InsightWarehouse].[dbo].[DimCurrency] as dc
 INNER  JOIN 
     [InsightWarehouse].[dbo].[FactCurrency] as fc 
     ON fc.CurrencyId = dc.CurrencyId
     AND fc.BusinessDate  = '2018-11-27'
     AND ( CurrencyCodeFrom <> 'KHR' OR CurrencyCodeTo <> 'KHR')

Resultado

CurrencyCodeFrom    CurrencyCodeTo  MidRevalRate    MultiplierRate  RevalRate   CurrencyMarketCode
THB                 KHR             122.500000000   122.500000000   1.000000000 1
THB                 KHR             122.750000000   122.750000000   1.000000000 10
THB                 KHR             122.500000000   122.500000000   1.000000000 3
THB                 KHR             122.750000000   122.750000000   1.000000000 5
THB                 KHR             122.750000000   122.750000000   1.000000000 7
THB                 KHR             122.750000000   122.750000000   1.000000000 9
USD                 KHR             4035.000000000  4035.000000000  1.000000000 1
USD                 KHR             4047.500000000  4047.500000000  1.000000000 10
USD                 KHR             4035.000000000  4035.000000000  1.000000000 3
USD                 KHR             4047.500000000  4047.500000000  1.000000000 5
USD                 KHR             4047.500000000  4047.500000000  1.000000000 7
USD                 KHR             4047.500000000  4047.500000000  1.000000000 9

Eu quero ordenar o CurrencyMarketCodeDESC por manter o grupoCurrencyCodeFrom CurrencyCodeTo

Deveria estar

CurrencyCodeFrom    CurrencyCodeTo  MidRevalRate    MultiplierRate  RevalRate   CurrencyMarketCode

THB                 KHR             122.500000000   122.500000000   1.000000000 1
THB                 KHR             122.500000000   122.500000000   1.000000000 3
THB                 KHR             122.750000000   122.750000000   1.000000000 5
THB                 KHR             122.750000000   122.750000000   1.000000000 7
THB                 KHR             122.750000000   122.750000000   1.000000000 9
THB                 KHR             122.750000000   122.750000000   1.000000000 10

USD                 KHR             4035.000000000  4035.000000000  1.000000000 1
USD                 KHR             4035.000000000  4035.000000000  1.000000000 3
USD                 KHR             4047.500000000  4047.500000000  1.000000000 5
USD                 KHR             4047.500000000  4047.500000000  1.000000000 7
USD                 KHR             4047.500000000  4047.500000000  1.000000000 9
USD                 KHR             4047.500000000  4047.500000000  1.000000000 10
sql-server

1 respostas

  1. Best Answer

    Seu comentário

    Eu quero classificar CurrencyMarketCode DESC mantendo o grupo CurrencyCodeFrom CurrencyCodeTo

    não corresponde ao seu resultado porque CurrencyMarketCodeno seu resultado está em ordem crescente.

    Como @ypercube comentou, por que não um simples

    ORDER BY CurrencyCodeFrom, CurrencyCodeTo, CurrencyMarketCoderesolver seu problema?

    --demo setup
drop table if exists table1
go
CREATE TABLE Table1 (
    [CurrencyCodeFrom] VARCHAR(3)
    ,[CurrencyCodeTo] VARCHAR(3)
    ,[MidRevalRate] DECIMAL(20, 9)
    ,[MultiplierRate] DECIMAL(20, 9)
    ,[RevalRate] DECIMAL(20, 9)
    ,[CurrencyMarketCode] int
    );    
INSERT INTO Table1
    ([CurrencyCodeFrom], [CurrencyCodeTo], [MidRevalRate], [MultiplierRate], [RevalRate], [CurrencyMarketCode])
VALUES
    ('THB', 'KHR', 122.500000000, 122.500000000, 1.000000000, 1),
    ('THB', 'KHR', 122.750000000, 122.750000000, 1.000000000, 10),
    ('THB', 'KHR', 122.500000000, 122.500000000, 1.000000000, 3),
    ('THB', 'KHR', 122.750000000, 122.750000000, 1.000000000, 5),
    ('THB', 'KHR', 122.750000000, 122.750000000, 1.000000000, 7),
    ('THB', 'KHR', 122.750000000, 122.750000000, 1.000000000, 9),
    ('USD', 'KHR', 4035.000000000, 4035.000000000, 1.000000000, 1),
    ('USD', 'KHR', 4047.500000000, 4047.500000000, 1.000000000, 10),
    ('USD', 'KHR', 4035.000000000, 4035.000000000, 1.000000000, 3),
    ('USD', 'KHR', 4047.500000000, 4047.500000000, 1.000000000, 5),
    ('USD', 'KHR', 4047.500000000, 4047.500000000, 1.000000000, 7),
    ('USD', 'KHR', 4047.500000000, 4047.500000000, 1.000000000, 9)
;

SELECT *
FROM table1
ORDER BY CurrencyCodeFrom
    ,CurrencyCodeTo
    ,CurrencyMarketCode;

    Os resultados parecem corresponder ao resultado desejado.

    | CurrencyCodeFrom | CurrencyCodeTo | MidRevalRate   | MultiplierRate | RevalRate   | CurrencyMarketCode |
|------------------|----------------|----------------|----------------|-------------|--------------------|
| THB              | KHR            | 122.500000000  | 122.500000000  | 1.000000000 | 1                  |
| THB              | KHR            | 122.500000000  | 122.500000000  | 1.000000000 | 3                  |
| THB              | KHR            | 122.750000000  | 122.750000000  | 1.000000000 | 5                  |
| THB              | KHR            | 122.750000000  | 122.750000000  | 1.000000000 | 7                  |
| THB              | KHR            | 122.750000000  | 122.750000000  | 1.000000000 | 9                  |
| THB              | KHR            | 122.750000000  | 122.750000000  | 1.000000000 | 10                 |
| USD              | KHR            | 4035.000000000 | 4035.000000000 | 1.000000000 | 1                  |
| USD              | KHR            | 4035.000000000 | 4035.000000000 | 1.000000000 | 3                  |
| USD              | KHR            | 4047.500000000 | 4047.500000000 | 1.000000000 | 5                  |
| USD              | KHR            | 4047.500000000 | 4047.500000000 | 1.000000000 | 7                  |
| USD              | KHR            | 4047.500000000 | 4047.500000000 | 1.000000000 | 9                  |
| USD              | KHR            | 4047.500000000 | 4047.500000000 | 1.000000000 | 10                 |
    • 3

relate perguntas