Sql-server – Generating a numeric pattern in query result set

sql serversql-server-2008-r2

I'm looking to add an artificial grouping to my result set. I can't utilize ROW_NUMBER or RANK.

Take the following result from SELECT Col1, Col2 FROM myTbl:

Col1     Col2
----     ----
x        y
x        z
y        x
z        x
x        z
y        x

I want to add a column that will count to 3 and then repeat so I can subdivide the result set if need be:

Divider  Col1     Col2
-------  ----     ----
1         x        y
2         x        z
3         y        x
1         z        x
2         x        z
3         y        x

Of course the easy way would be to loop through the result set with a cursor or temp table and add it on, but when the result set is 4 million rows, performance will be terrible.

Anyone know how to add this Divider column directly into a SELECT statement?

In reality, the query will retrieve the result set ordered by date but I still want the divider to count off in a fixed manner. If I would try to partition and use row_number the sequence would be unpredictable. Suppose I wanted to take the larger result set and spread it out over a set of queues but I want them evenly distributed by date to make sure each queue is processing the most recent data. If I would order by date and subdivide only one of the queues would get the most recent data.

I'm not actually returning the date in the result set; I am only ordering by it.

Best Answer

Based on your comments you will be ordering the results by date. In that case you could use

ROW_Number () over( order by date)

Which would output the row numbers. You could then use the SQL Modulo function, % and perform a %3 which would give you 1,2,0 repeating as the row output which gives you the grouping you want.

Related Solutions

Sql-server – How to Select Most Recently Updated Row Based on Sequence ID

;WITH x AS
(
  SELECT Person_ID, Person_Seq_ID, Person_Name, Person_Favorite_Color,
    rn = ROW_NUMBER() OVER (PARTITION BY Person_ID ORDER BY Person_Seq_ID DESC)
  FROM dbo.Person -- always use schema prefix
)
SELECT Person_ID, Person_Seq_ID, Person_Name, Person_Favorite_Color
  FROM x WHERE rn = 1
  ORDER BY Person_ID;

Sql-server – the best way to get all data for a date range, plus the last event just before the range

I am going to assume that there isn't an index on the date columns, otherwise I think that the query would have been structured differently. If there is, you can probably find a better performing one than this.

The advantage of this query is that it can get all the data in one scan. The disadvantage is that it has to sort the data and join EventEmployee on the entire table. So as always, test with your own situation. This query also assumes that the MAX date is either unique or that equivalent rows would be acceptable.

USE AdventureWorks2012
GO
;
WITH Base AS (
   SELECT 
      TransactionHistory.*
      ,ProductVendor.BusinessEntityID
      ,MAX(CASE WHEN TransactionDate < '2008-08-01' THEN TransactionDate END) 
           OVER (PARTITION BY ProductVendor.BusinessEntityID) AS PreviousVendorTransaction
      ,COUNT(CASE WHEN TransactionDate >= '2008-08-01' THEN 1 END ) 
           OVER (PARTITION BY ProductVendor.BusinessEntityID) AS VendorAfterCutoff
   FROM
      Production.TransactionHistory
      -- Doesn't make the most sense, but I need a repeating relation
      INNER JOIN Purchasing.ProductVendor
         ON TransactionHistory.ProductID = ProductVendor.ProductID
),
Filtered AS (
   SELECT
      *
   FROM
      Base
   WHERE
      Base.TransactionDate >= '2008-08-01'
      OR (TransactionDate = PreviousVendorTransaction AND VendorAfterCutoff > 0)
)
SELECT DISTINCT
   TransactionID
   ,ProductID
   ,ReferenceOrderID
   ,ReferenceOrderLineID
   ,TransactionDate
   ,TransactionType
   ,Quantity
   ,ActualCost
   ,ModifiedDate
FROM
   Filtered

Edit:

Hmm, I think I may have to take back my comment on structuring it differently if there are indexes. The other suggestions that I have are probably fairly minor.

Make sure the query is using the indexes you're expecting it to. Start and End date to build temp table, end date to drive the previous event loop.
If the query to build the temp table is doing a lookup on the clustered index, it may be better to hold off and do that as part of the main query.
Try using a cte instead of a temp table. I think that a cte might be more competitive with the way that the query is structured below.
If you are returning a lot of events, it might be better to pull out the event table lookup to the main query to give the optimizer the option of doing a merge join.
I don't see a way of optimizing the previous event lookup short of an indexed view.

Here's a query that combines a few of those ideas.

SELECT
    e.[EventID]
INTO #EventTemp
FROM
    [Events] AS e
WHERE
    ( e.[EventStart] >= @StartDate AND e.[EventStart] <= @EndDate )
    OR ( e.[EventEnd] >= @StartDate AND e.[EventEnd] <= @EndDate )

;
WITH PrevEvent AS (
    SELECT
        EmpPrevEvent.[EventID]
    FROM
    (
        SELECT DISTINCT
            ee.[EmployeeID]
        FROM
            #EventTemp
            INNER JOIN [EventEmployee] AS ee ON
                #EventTemp.[EventID] = ee.[EventID]
    ) AS Emp
    CROSS APPLY (
        SELECT TOP 1
            e.[EventID]
        FROM
            [Events] AS e
            INNER JOIN [EventEmployee] AS ee ON
                e.[EventID] = ee.[EventID]
        WHERE
            ee.[EmployeeID] = Emp.[EmployeeID]
            AND e.[EventEnd] < @StartDate
        ORDER BY 
            e.[EventEnd] DESC
    ) AS EmpPrevEvent
)
SELECT
    e.[EventID],
    e.[EventStart],
    e.[EventEnd],
    e.[EventTypeID]
FROM
    [Events] AS e
WHERE
    e.EventID IN (
        SELECT EventID
        FROM #EventTemp
        UNION
        SELECT EventID
        FROM PrevEvent
    )

Best Answer

Related Solutions

Sql-server – How to Select Most Recently Updated Row Based on Sequence ID

Sql-server – the best way to get all data for a date range, plus the last event just before the range

Related Question