SQL Server API Learning

SQL Server API Learning Server

• Ranking function
  • NTILE
• An expression
  • Coalesce
  • Iif
  • CHOOSE

Ranking function ntile

Grammar:

NTILE (integer_expression) over ([<partition_by_clause>] < Order_by_clause >)

return : bigint
integer_expression is divisible by the order specified by the OVER clause, and the larger group is preceded by a smaller group. For example: 53 is divided into 5 groups, then the first 3 groups are 11 rows, and the latter 2 groups are 10 rows.
The quantity is integer_expression are grouped into groups, each with its own number and the uncorrupted set number starting at 1.
a: Grouping rows into groups

2. < Span class= "Li_linenum_before_span li_linenum_before_span_hide" style= "Content:counter (lines, decimal); Position:absolute; left:0px; Text-align:center; Width:2.5em; opacity:0.5; Vertical-align:top; Display:none; " >1 select p. FirstName, p.lastname  
4. < Span class= "Li_linenum_before_span li_linenum_before_span_hide" style= "Content:counter (lines, decimal); Position:absolute; left:0px; Text-align:center; Width:2.5em; opacity:0.5; Vertical-align:top; Display:none; " >2 , NTILE ( 4 ) over ( order by s.salesytd desc ) as quartile  
6. < Span class= "Li_linenum_before_span li_linenum_before_span_hide" style= "Content:counter (lines, decimal); Position:absolute; left:0px; Text-align:center; Width:2.5em; opacity:0.5; Vertical-align:top; Display:none; " >3 CONVERT (nvarchar (20 ), S.salesytd,1 ) as SalesYTD  
8. < Span class= "Li_linenum_before_span li_linenum_before_span_hide" style= "Content:counter (lines, decimal); Position:absolute; left:0px; Text-align:center; Width:2.5em; opacity:0.5; Vertical-align:top; Display:none; " >4 A.postalcode  
10. < Span class= "Li_linenum_before_span li_linenum_before_span_hide" style= "Content:counter (lines, decimal); Position:absolute; left:0px; Text-align:center; Width:2.5em; opacity:0.5; Vertical-align:top; Display:none; " >5 From Sales.SalesPerson as s  
12. < Span class= "Li_linenum_before_span li_linenum_before_span_hide" style= "Content:counter (lines, decimal); Position:absolute; left:0px; Text-align:center; Width:2.5em; opacity:0.5; Vertical-align:top; Display:none; " >6 INNER as P  
14. < Span class= "Li_linenum_before_span li_linenum_before_span_hide" style= "Content:counter (lines, decimal); Position:absolute; left:0px; Text-align:center; Width:2.5em; opacity:0.5; Vertical-align:top; Display:none; " >7 on S.businessentityid = P.businessentityid  
16. < Span class= "Li_linenum_before_span li_linenum_before_span_hide" style= "Content:counter (lines, decimal); Position:absolute; left:0px; Text-align:center; Width:2.5em; opacity:0.5; Vertical-align:top; Display:none; " >8 INNER as a  
18. < Span class= "Li_linenum_before_span li_linenum_before_span_hide" style= "Content:counter (lines, decimal); Position:absolute; left:0px; Text-align:center; Width:2.5em; opacity:0.5; Vertical-align:top; Display:none; " >9 on a.addressid = P.businessentityid  
20. Ten WHERE TerritoryID is not null

B: Using partition by to divide the result set

1. 1

Expression COALESCE

Computes the variable in order and returns the current value of the first non- NULL expression

COALESCE (expression,.......)

PS: If all values are null, at least one null is returned
The coalesce expression is a syntax shortcut to a case expression. That is, the query optimizer overrides code coalesce (expression1,... N) to the following case expression:

2. 1 Case
4. 2 When (expression1 isn't NULL) then expression1
6. 3 When (expression2 isn't NULL) then expression2
8. 4 ...
10. < Span class= "Li_linenum_before_span li_linenum_before_span_hide" style= "Content:counter (lines, decimal); Position:absolute; left:0px; Text-align:center; Width:2.5em; opacity:0.5; Vertical-align:top; Display:none; " >5 ELSE expressionn 
12. 6 END

Simple Sample code:

2. < Span class= "Li_linenum_before_span li_linenum_before_span_hide" style= "Content:counter (lines, decimal); Position:absolute; left:0px; Text-align:center; Width:2.5em; opacity:0.5; Vertical-align:top; Display:none; " >1 select name, Class, Color, ProductNumber,  
4. < Span class= "Li_linenum_before_span li_linenum_before_span_hide" style= "Content:counter (lines, decimal); Position:absolute; left:0px; Text-align:center; Width:2.5em; opacity:0.5; Vertical-align:top; Display:none; " >2 Coalesce (class,color,productnumber) as Firstnotnull  
6. 3 From production.product

Iif

Calculate different branching expressions based on ture and false
Grammar:

iif (Logical_Expression, Expression1 [HINT], Expression2 [HINT])
IIf function has three parameters: IIf (< conditions;, <then Branch;, <else branch >)

1. 1

CHOOSE

Returns the item at the specified index from the list of values in SQL Server.
Grammar:

CHOOSE (index, Val_1, val_2 [, Val_n])
Parameters :
Index
An integer expression that represents the 1-based index of the list of items after it. If the supplied index value has a numeric data type other than int, the value is implicitly converted to an integer. If the index value is outside the bounds of the value array, CHOOSE returns NULL.
Val_1 ... val_n
A comma-separated list of values for any data type.

Example:

2. < Span class= "Li_linenum_before_span li_linenum_before_span_hide" style= "Content:counter (lines, decimal); Position:absolute; left:0px; Text-align:center; Width:2.5em; opacity:0.5; Vertical-align:top; Display:none; " >1 select choose (2 , ' test1 ' , ' test2 ' ) as result  

SQL Server API Learning