-1

i never use sql server ROW_NUMBER() function. so i read some article regarding ROW_NUMBER(),PARTITION & RANK() etc but still not clear to me.

i found the syntax is like

SELECT top 10 ROW_NUMBER() OVER(ORDER BY JID DESC) AS 'Row Number',
JID,Specialist,  jobstate, jobtype FROM bbajobs

SELECT top 10 ROW_NUMBER() OVER(PARTITION  BY JID ORDER BY JID DESC) AS 'Row Number',
JID,Specialist,  jobstate, jobtype FROM bbajobs

i have few question

1) what over() function does. why we need to specify column name in over function like OVER(ORDER BY JID DESC)

2) i saw sometime people use PARTITION  keyword. what it is?
it is also used in over function like OVER(PARTITION  BY JID ORDER BY JID DESC)
3) in what type of situation we have to use PARTITION  keyword
4) when we specify PARTITION  keyword in over then also we need to specify order by    also why. only PARTITION  keyword can not be used in over clause.
5) what type of situation one should use RANK function
6) what is CTE and what is the advantage of using CTE. it is just like temporary view.
anyone get any performance boost if he/she use CTE other than reusability?

please discuss my points in detail. it will be very much helpful if some one make me understand with small & easy example for all the keyword like ROW_NUMBER(),PARTITION & RANK(). thanks

Thomas
  • 33,544
  • 126
  • 357
  • 626

2 Answers2

4
Andriy M
  • 76,112
  • 17
  • 94
  • 154
rejj
  • 1,216
  • 7
  • 13
1

  1. You need ORDER BY because sets have no order otherwise. You need it for a standard SELECT

  2. PARTITION BY resets the COUNT per partition

  3. Many

  4. See point 1. You can use PARTITION by itself for SUM, COUNT etc

  5. See MSDN

  6. Separate question

gbn
  • 422,506
  • 82
  • 585
  • 676
  • PARTITION BY use just like group by clause? – Thomas Jan 11 '12 at 08:42
  • what is the advantage of using CTE. it is just like temporary view. is there any extra benefit of using CTE? – Thomas Jan 11 '12 at 08:43
  • @Thomas: no. PARTITION BY allows an "inline" aggregates or muliple GROUP BYs. Examples: http://stackoverflow.com/a/8324882/27535 and http://stackoverflow.com/a/6219064/27535 – gbn Jan 11 '12 at 09:35
  • @Thomas: for CTE, see this http://stackoverflow.com/a/698634/27535 Note, a view is a macro and works like a CTE anyway. Also http://stackoverflow.com/a/3711769/27535 – gbn Jan 11 '12 at 09:35