Prev: error when trying to alter table in transaction
Next: On Update Cascade. Should I use it?
From: alexcn on 13 Jul 2010 09:35
HI Mark,

I see what you mean, yes I would now tend to agree that CTE's are, for
the most part, unavoidable. It's a shame that you cant use one of the
ranking functions to number the different groups sequentially that are
"Winner" or "Loser" as you could then use this as part of the
partition to then reset the ROW_NUMBER function for what would then be
separate groups of Winner1 and Winner2 and so forth or otherwise
include some kind of "reset partition on change of x column" which
would then make running totals over grouped data more easily
available.

Luckily I actually have a query as part of the FROM clause anyway and
then summarise this above, so I can just include the two ROW_NUMBER
functions in this subquery and the upper RN function using rnDiff in
the next query up.

Thanks for your help though, very inspiring.

Alex

On 13 July, 13:43, Mark <markc...(a)hotmail.com> wrote:
> On 13 July, 13:25, alexcn <ale...(a)writeme.com> wrote:
>
>
>
> > Dear Mark,
>
> > Many thanks for your prompt reply, it works perfectly so thanks for
> > your efforts.  I was just wondering whether you could think of a
> > possible solution again using the SQL server ranking functions that
> > dont use a CTE? Just that this is already part of a much larger query
> > which I would prefer not to wrap up or split away in a CTE thats all.
>
> > Thanks once again,
>
> > Alex
>
> > On 13 July, 11:34, Mark <markc...(a)hotmail.com> wrote:
>
> > > CREATE TABLE Accounts(Account CHAR(7), Currency CHAR(3), TrdDate
> > > DATETIME,  EntryTime DATETIME,  PLInd VARCHAR(10));
>
> > > INSERT INTO Accounts(Account, Currency, TrdDate,  EntryTime,  PLInd)
> > > SELECT 'AAA0007', 'EUR' ,     '20100609' ,     '20100609
> > > 08:25:20' ,    'Winner'  UNION ALL
> > > SELECT 'AAA0007', 'EUR' ,     '20100609' ,     '20100609
> > > 09:18:57' ,    'Winner'  UNION ALL
> > > SELECT 'AAA0007', 'EUR' ,     '20100609' ,     '20100609
> > > 09:27:51' ,    'Winner'  UNION ALL
> > > SELECT 'AAA0007', 'EUR' ,     '20100609' ,     '20100609
> > > 10:18:07' ,    'Winner'  UNION ALL
> > > SELECT 'AAA0007', 'EUR' ,     '20100609' ,     '20100609
> > > 10:49:22' ,    'Loser '  UNION ALL
> > > SELECT 'AAA0007', 'EUR' ,     '20100609' ,     '20100609
> > > 11:03:09' ,    'Winner'  UNION ALL
> > > SELECT 'AAA0007', 'EUR' ,     '20100609' ,     '20100609
> > > 11:38:56' ,    'Winner'  UNION ALL
> > > SELECT 'AAA0007', 'EUR' ,     '20100609' ,     '20100609
> > > 12:39:25' ,    'Loser '  UNION ALL
> > > SELECT 'AAA0007', 'EUR' ,     '20100609' ,     '20100609
> > > 13:26:16' ,    'Winner'  UNION ALL
> > > SELECT 'AAA0007', 'EUR' ,     '20100609' ,     '20100609
> > > 14:30:31' ,    'Winner'  UNION ALL
> > > SELECT 'AAA0007', 'EUR' ,     '20100609' ,     '20100609
> > > 14:46:59' ,    'Winner';
>
> > > WITH CTE AS (
> > >  SELECT Account, Currency, TrdDate,  EntryTime,  PLInd,
> > >         ROW_NUMBER() OVER(PARTITION BY TrdDate, Account, PLInd ORDER
> > > BY EntryTime) -
> > >         ROW_NUMBER() OVER(PARTITION BY TrdDate, Account ORDER BY
> > > EntryTime) AS rnDiff
> > >  FROM Accounts)
> > > SELECT Account, Currency, TrdDate,  EntryTime,  PLInd,
> > >     ROW_NUMBER() OVER(PARTITION BY TrdDate, Account, PLInd, rnDiff
> > > ORDER BY EntryTime)
> > > FROM CTE
> > > ORDER BY EntryTime;- Hide quoted text -
>
> > - Show quoted text -
>
> I think the CTE is unavoidable - SQL Server doesn't support nesting of
> ranking functions, you get messages such as this
>
> "Windowed functions cannot be used in the context of another windowed
> function or aggregate"
>
> so the solution I used is to split the query into separate CTEs.

First  |  Prev  | 
Pages: 1 2
Prev: error when trying to alter table in transaction
Next: On Update Cascade. Should I use it?