-1

I've inherited the following query and I'm not sure what the second value being selected is.

Can anyone please shed some light?

Sorry if the question is vague but I've given as many details as I understand.

SELECT
dateId,
row_number() OVER (order by DATEPART(ISO_WEEK, min(myDate)) % 2, myDateId-1) AS DayIdx 
FROM table
WHERE masterID=9084141 GROUP BY dateId
MANOJ GOPI
  • 1,279
  • 10
  • 31
Tom
  • 12,776
  • 48
  • 145
  • 240
  • possible duplicate of [OVER() clause. When and why is it useful?](http://stackoverflow.com/questions/6218902/over-clause-when-and-why-is-it-useful) – demo Feb 02 '15 at 16:50
  • 1
    Have you even attempted to google this, it is the first result in 'over sql server' – ChrisBint Feb 02 '15 at 16:50

2 Answers2

3

The row_number() is a ranking function used to get a sequential value within a portion of the data. The DatePart is going to return the week number in smallest to largest value. In this query all values with the same week number will align by the sequence number or row_number.

Ross Bush
  • 14,648
  • 2
  • 32
  • 55
0

The OVER clause defines a window or user-specified set of rows within a query result set. MSDN link:- https://msdn.microsoft.com/en-us/library/ms189461.aspx

Its got 2 important arguments:

Partition By : Sets up the partition pretty much like a Group by for you function to be applied on top of.

Order BY : Sets the order of the result set

You could use it with RANKING functions like in your query OR you could it with an aggregation function as well. Example of an use with aggregate function:

select 
customerID
, productID
, sum(orderAmount) OVER (Partition by CustomerID) as Total 
from Orders
SoulTrain
  • 1,904
  • 1
  • 12
  • 11