Đỉnh NGUYỄN

life's a journey not a destination

Tìm giá trị lớn nhất trên mỗi dòng – SQL Server

Leave a comment


Có 1 cách đơn giản và hiệu quả để tìm giá trị lớn nhất trên mỗi dòng dùng SQL Server UNPIVOT

DECLARE @t table (id int PRIMARY KEY, col1 int, col2 int, col3 int)
-- SAMPLE DATA
INSERT INTO @t SELECT 1, 45, 2, 14
INSERT INTO @t SELECT 2, 8, 1, 12
INSERT INTO @t SELECT 3, 21, 20, 8
INSERT INTO @t SELECT 4, 8, 5, 2
INSERT INTO @t SELECT 5, 23, 49, 7
INSERT INTO @t SELECT 6, 19, 7, 5
INSERT INTO @t SELECT 7, 7, 7, 2
INSERT INTO @t SELECT 8, 14, 17, 2
-- QUERY
SELECT id, MAX(col) AS maxValue
FROM
(
    SELECT id, col
    FROM @t
    UNPIVOT (col FOR ListofColumns IN (col1,col2,col3)) AS unpivott
)
AS p
GROUP BY id

Kết quả

image

Nếu chưa quen với UNPIVOT, đọc Using PIVOT and UNPIVOT

Advertisements

Author: dinhnn

Senior software developer, a technical leader. You can be reached at via email to dinhnguyenngoc@gmail.com, via my blog at dinhnguyenngoc.wordpress.com, and on Twitter @dinhnguyenngoc.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s