iT邦幫忙

0

[MS SQL] Group by 後查詢出第一筆

想了解下表如何查詢出Group by customer後的每項第一筆資料:

id customer total
1 Joe AA
2 Sally CC
3 Joe BB
4 Sally DD

結果:

FIRST(id) customer FIRST(total)
1 Joe AA
    1 | Joe      | AA
    2 | Sally    | CC

2 個回答

3
glj8989332
iT邦新手 1 級 ‧ 2020-07-27 14:22:47

partition by的語法

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Id DESC) AS rn
   FROM MyCustomer
)
SELECT Id, Customer, Total
FROM cte
WHERE rn = 1

可成功的範例
SQL Fiddle

0
rogeryao
iT邦大師 1 級 ‧ 2020-07-27 15:23:47
CREATE TABLE Test (
  [id] int,
  [customer] varchar(20),
  [total] varchar(20)
);

INSERT INTO Test ([id], [customer], [total])
  VALUES (1, 'Joe', 'AA'),
  (2, 'Sally', 'CC'),
  (3, 'Joe', 'BB'),
  (4, 'Sally', 'DD');

SELECT  *
FROM Test;
SELECT A.newid as 'FIRST(id)',A.customer,B.total as 'FIRST(total)'
FROM (
SELECT MIN(id) AS newid,customer
FROM Test
GROUP BY customer) AS A
LEFT JOIN Test AS B ON A.newid = B.id AND A.customer = B.customer
ORDER BY A.customer, A.newid

Demo

我要發表回答

立即登入回答