Re: Dynamic Comma delimited output
From: Steve Kass (skass_at_drew.edu)
Date: 12/14/04
- Next message: anonymous_at_discussions.microsoft.com: "RE: Can Not Access DB"
- Previous message: Paul fpvt2: "Re: How can I assign the result of dateadd to a variable ?"
- In reply to: Adam Machanic: "Re: Dynamic Comma delimited output"
- Next in thread: Adam Machanic: "Re: Dynamic Comma delimited output"
- Reply: Adam Machanic: "Re: Dynamic Comma delimited output"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 14 Dec 2004 12:48:18 -0500
Adam,
Ok, I think I've gone on a useless tangent. First of all, RANK
returns a value for each row, and is a different kind of aggregate than
one returning one row per grouping, so I shouldn't be trying to use that
example. We're thinking more about something like this:
SELECT CustomerID, CONCAT_AGG(OrderID) OVER (ORDER BY OrderID desc) as
OrderList
FROM Orders
GROUP BY CustomerID
The OVER syntax in SQL for
SELECT OrderID, RANK() OVER (ORDER BY CustomerID, OrderID desc) as R
FROM Orders
isn't relevant here, since there is no aggregating going on. Better
would be to look at something like
SELECT CustomerID, FIRST(OrderID) OVER (ORDER BY EmployeeID desc) as R
FROM Orders
GROUP BY CustomerID
if that were something that already exists.
The first OrderID for each CustomerID in EmployeeID desc order can be
found without sorting, so I think I'm still on track, but let me think
some more and get back to you...
SK
Adam Machanic wrote:
>"Steve Kass" <skass@drew.edu> wrote in message
>news:uqf%23xjf4EHA.4004@tk2msftngp13.phx.gbl...
>
>
>> I don't have my 2005 box running at the moment, but the sort in the
>>plan doesn't invalidate what I said. rank() has a set-theoretical
>>definition, and can be calculated by aggregating the CASE expression I
>>gave without a sort. The reason you see a sort is because the optimizer
>>chooses to generate the rank() values by sorting first, not because
>>sorting is required. For all I know, no alternative option for RANK()
>>
>>
>
>
>Okay, at this point I'm in over my head, as I don't really understand how
>the windowing that you're referring to actually works (specifically, "Just
>evaluate case when s in window and s <= x then 1 else 0 end for each row and
>keep a running sum.") So I'm going to go track down some resources on that
>(if you have any recommendations, please send them my way) and then I'll
>hopefully either agree with you or have something more to add to this
>discussion at a later date. :)
>
>I can't imagine how such an algorithm could possibly work efficiently
>without sorting the rows, as it could have no knowledge of the other rows'
>placement/numbers otherwise... e.g. if I ask for a rank based on a
>descending sort on one column, how can an algorithm identify a given row
>without knowing which rows preceed it? One way, and perhaps this is what
>you're referring to, is that it could scan the intermediate rowset to count
>the rows for each group, similar to what we do now when we use COUNT(*) to
>generate rankings. That would not require intermediate sorting, but is
>incredibly expensive and certainly much less efficient than an intermediate
>sort would be. So I'm assuming that the type of windowing operation you're
>referring to does something else that's both efficient and doesn't require
>sorting?
>
>
>
>
- Next message: anonymous_at_discussions.microsoft.com: "RE: Can Not Access DB"
- Previous message: Paul fpvt2: "Re: How can I assign the result of dateadd to a variable ?"
- In reply to: Adam Machanic: "Re: Dynamic Comma delimited output"
- Next in thread: Adam Machanic: "Re: Dynamic Comma delimited output"
- Reply: Adam Machanic: "Re: Dynamic Comma delimited output"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
