การใช้คำสั่ง ROW_NUMBER ใน MSSQL 2008-R2

Posted by on March 31, 2015

สวัสดีผู้อ่านทุกๆคนครับ,

ยังคงอยู่กันที่การใช้งาน MSSQL 2008 R2 น่ะครับ

ผมมีคำสั่งที่น่าสนใจ ที่ใช้งานบ่อยมากๆครับ นั่นคือ ROW_NUMBER ครับ

 

จุดประสงค์

– การสร้างลำดับของตัวเลขตามลำดับที่เราต้องการครับ

 

ลักษณะการใช้งาน

– การ Order ทั้งแบบพื้นฐานและแบบซับซ้อนในกรณีของการ Query สำหรับ Historical Data หรือพวกข้อมูลตัวเลขต่างๆ

– การ ลบข้อมูลที่มีความซ้ำซ้อนกันในกรณีของการ Import Data

 

มาดูการใช้งานกันเลยน่ะครับ…

คำถาม: Record ไหนคือข้อมูลที่แก้ไขล่าสุดของแต่ล่ะ CompanyTitle, ContactName จาก ตารางข้างล่างนี้…

Table1

 

คำตอบ: ดูได้จากตารางข้างล่างน่ะครับ

Table2

 

ตัวอย่าง T-SQL สำหรับคำตอบ: ดูได้จากตารางข้างล่างน่ะครับ


 

SELECT *

FROM

(

SELECT

Id, CompanyTitle, ContactName, LastContactDate,

RN = ROW_NUMBER() OVER (PARTITION BY CompanyTitle, ContactName ORDER BY LastContactDate DESC)

FROM Suppliers

) as RankingTable

WHERE

RN = 1


Capture

 

คำอธิบาย T-SQL : ดูได้จากตารางข้างล่างน่ะครับ

1.ทำการเรียงลำดับ ด้วย LastContactDate DESC จากคำสั่ง ORDER BY

2.ทำการเรียงลำดับชุดข้อมูลที่เราสนใจ  ในที่นี้คือ CompanyTitle, ContactName จาก คำสั่ง PARTITION BY

3. ได้ผลลัพธ์ดังนี้

Table3

4. จากรูปข้างบนจะเห็นได้ว่า… ตัวเลข RN จะมีการเพิ่มขึ้น จาก 1,2,…. เรื่อยๆ ตาม group ของ CompanyTitle, ContactName ที่เปลี่ยนแปลง, ซึ่ง 1 จะหมายถึงชุดข้อมูลที่แก้ไขล่าสุด เพราะเรา order ด้วย LastContactDate DESC

5. เราสามารถทำการหาเฉพาะข้อมูลที่แก้ไขล่าสุดของแต่ล่ะ CompanyTitle, ContactName  ด้วยการเขียน sub query ครอบอีก 1 ชั้น และ เขียน where RN= 1 ซึ่งได้อธิบายไว้ใน ตัวอย่าง T-SQL สำหรับคำตอบ แล้ว

 

หมายเหตุ:

1. เราสามารถนำ ROW_NUMBER ไปประยุกต์กับการลบข้อมูลที่มีความซ้ำซ้อนกันได้ด้วยน่ะครับ ลองดูตัวอย่างตามลิ้งข้างล่างประกอบ

T-SQL ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) to Delete Duplicate Rows in SQL Table

http://www.kodyaz.com/articles/delete-duplicate-rows-using-row-number-partition-by-order-by.aspx

2. เราสามารถใช้ ROW_NUMBER ได้ หลายๆ columns พร้อมกันน่ะครับ เช่น RN1, RN2, RN3 ซึ่งแต่ล่ะอันก็จะ order ตามแต่ล่ะจุดประสงค์ซึ่งจะช่วยทำให้เขียน query ที่ซับซ้อนได้มากยิ่งขึ้นครับ

 

สุดท้ายนี้ หวังว่าบทความนี้คงจะมีประโยชน์ให้กับคุณผู้อ่านทุกท่านในการทำไปใช้งานกับ MSSQL น่ะคร้าบบบบบบ