T-SQL LANGUAGE ENHANCEMENTS ROW_NUMBERproduces a monotonically increasing number;

T-SQL LANGUAGE ENHANCEMENTS ROW_NUMBERproduces a monotonically increasing number; that is, no rows will ever share a ROW_NUMBER. SELECT orderid, customerid, ROW_NUMBER() OVER(ORDER BY customerid) AS num FROM orders WHERE orderid < 10400 AND customerid <= BN produces orderid customerid num - 10308 ANATR 1 10365 ANTON 2 10355 AROUT 3 10383 AROUT 4 10384 BERGS 5 10278 BERGS 6 10280 BERGS 7 10265 BLONP 8 10297 BLONP 9 10360 BLONP 10 RANK() applies a monotonically increasing number for each value in the set. The value of ties, however, is the same. If the columns in the OVER(ORDER BY ) clause have unique values, the result produced by RANK() is identical to the result produced by ROW_NUMBER(). RANK() and ROW_NUMBER() differ only if there are ties. Here s the second earlier example using RANK(). SELECT orderid, customerid, RANK() OVER(ORDER BY customerid) AS [rank] FROM orders WHERE orderid < 10400 AND customerid <= BN produces orderid customerid rank - 10308 ANATR 1 10365 ANTON 2 10355 AROUT 3 10383 AROUT 3 10384 BERGS 5 10278 BERGS 5 10280 BERGS 5 10265 BLONP 8 Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

Bookmark the permalink.

Comments are closed.