SQL tomonidan TOP 10 MAX qiymat guruhini tanlang

I have the following table:

-----------------------------------------
xDate        xItem       xCount
-----------------------------------------
2018-01-01   A           100
2018-01-01   B           200
2018-01-01   D           500
2018-01-02   C           200
2018-01-02   E           800

I want to select TOP 2 value for each date on the MAX value of xCount field. So, the result should be:

-----------------------------------------
xDate        xItem      xCount
-----------------------------------------
2018-01-01   D          500
2018-01-01   B          200
2018-01-02   E          800
2018-01-02   C          200

Bu savolga kimdir tushundimi? Cheers,

1

5 javoblar

RANK oyna vazifasini ishlatishingiz mumkin, agar ikkala satrda bir xil xCount bo'lsa, unda siz ularni olishni xohlaysiz.

You can try to use dense_rank instead of RANK

SELECT xDate,xItem,xCount
FROM (
    SELECT *,RANK() OVER(PARTITION BY xDate ORDER BY xCount DESC) rn
    FROM T
) t1
WHERE t1.rn <= 2
2
qo'shib qo'ydi
Xos qiymatning 2dan ortiq yozuvlari mavjud bo'lsa, unda uchinchi yozuv sifatida ko'rsatiladi. To'g'ri?
qo'shib qo'ydi muallif Haminteu, manba

Siz ROW_NUMBER() dan foydalansangiz va uni xDate asosida ajratishingiz va kerakli narsani olish uchun xCount buyurtma berishingiz mumkin.

select xDate, 
       xItem, 
       xCount
  from 
       (select xDate, 
               xItem, 
               xCount, 
               row_number() over (partition by xDate order by xCount desc) rank_of_count
         from table_name)
 where rank_of_count < 3
1
qo'shib qo'ydi

DENSE_RANK() dan foydalangan holda yana bitta taklif:

DECLARE @FooTable TABLE
(
    xDate VARCHAR(25),
    xItem VARCHAR(10),
    xCount INT  
)

INSERT INTO @FooTable
(
    xDate,
    xItem,
    xCount
)
VALUES
  ('2018-01-01',   'A',           100)
, ('2018-01-01',   'B',           200)
, ('2018-01-01',   'D',           500)
, ('2018-01-02',   'C',           200)
, ('2018-01-02',   'E',           800)

SELECT 
s.*
FROM 
(
    SELECT 
      ft.xDate
    , ft.xItem
    , ft.xCount
    --, ROW_NUMBER() OVER(PARTITION BY ft.xDate ORDER BY ft.xCount DESC) rn
    , DENSE_RANK() OVER (PARTITION BY ft.xDate ORDER BY ft.xCount desc) dr
    FROM @FooTable ft
)s
WHERE s.dr < 3
0
qo'shib qo'ydi

Xo'sh, men hech qanday funktsiyani ishlatmas edim. Menga juda to'g'ri keladi. Eng tezkor bo'ladi

SELECT * FROM   #temp s
WHERE ( SELECT  COUNT(*)  FROM    #temp  f WHERE f.xDate = s.xDate AND  f.xCount >= s.xCount ) <= 2
Order by xDate, xCount desc

To'liq namunali kodni bu erda tekshiring:

create table #temp (xDate datetime, xItem nvarchar(max), xCount int);

insert into #temp
select
'2018-01-01','A', 100 union all 
select '2018-01-01','B', 200 union all 
select '2018-01-01','D', 500 union all 
select '2018-01-02','C', 200 union all 
select '2018-01-02','E', 800


SELECT * FROM   #temp s
WHERE ( SELECT  COUNT(*)  FROM    #temp  f WHERE f.xDate = s.xDate AND  f.xCount >= s.xCount ) <= 2
Order by xDate, xCount desc

drop table #temp;
0
qo'shib qo'ydi

Boshqa usul - TOP, CROSS APPLY dan foydalanish.

;WITH CTE AS
(
select 
distinct xDate
from Your_Table
)
SELECT 
T.xDate,
T1.xItem,
T1.xCount
FROM CTE T
CROSS APPLY (SELECT TOP 2 xItem,xCount FROM Your_Table WHERE xDate=T.xDate ORDER BY xCount DESC ) T1
0
qo'shib qo'ydi