Đỉnh NGUYỄN

life's a journey not a destination

Từ khóa TOP trong SQL Server với WITH TIES

1 Comment


Ví dụ sau gồm 10 mẫu tin

DECLARE @t TABLE(id int IDENTITY(1,1) PRIMARY KEY, title NVARCHAR(256) NOT NULL, amount INT)

INSERT INTO @t(title, amount) VALUES('title 1', 1)
INSERT INTO @t(title, amount) VALUES('title 2', 2)
INSERT INTO @t(title, amount) VALUES('title 3', 3)
INSERT INTO @t(title, amount) VALUES('title 4', 4)
INSERT INTO @t(title, amount) VALUES('title 5', 5)
INSERT INTO @t(title, amount) VALUES('title 6', 6)
INSERT INTO @t(title, amount) VALUES('title 7', 7)
INSERT INTO @t(title, amount) VALUES('title 8', 8)
INSERT INTO @t(title, amount) VALUES('title 9', 9)
INSERT INTO @t(title, amount) VALUES('title 0', 5)

SELECT * FROM @t ORDER BY amount DESC

Kết quả

image

Ý định sẽ phải lấy ra 5 mẫu tin có amount cao nhất, nếu có hơn 5 mẫu tin có cùng amount cao nhất cũng phải lấy ra luôn

SELECT TOP 5 * FROM @t ORDER BY amount DESC

Kết quả chỉ là 5 (thiếu 1 mẫu tin có cùng amount là 5)

image

Với WITH TIES

SELECT TOP 5 WITH TIES * FROM @t ORDER BY amount DESC

Kết quả

image

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.

One thought on “Từ khóa TOP trong SQL Server với WITH TIES

  1. thanks you !! ví dụ dễ hiểu

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