It is the ROW_NUMBER() example in the above link which is likely to be the most efficient. eg

SELECT D.BossID, D.KillID

FROM (

        SELECT ROW_NUMBER() OVER (PARTITION BY BossID ORDER BY KillID) AS...

I do not know MySQL but you could try standard ANSI syntax:

UPDATE tblSchedule

SET HomeScore = 14

    ,AwayScore = 17

WHERE HomeTeamID = (

            SELECT T1.[id]

            FROM tblTeams T1

            WHERE T1.FirstName = 'New England'

                AND T1.LastName =...

As has already been mentioned, IN is generally not a good idea.

The most efficient queries usually use either JOINs or EXISTs depending on what you want.

-- eg of IN

SELECT *

FROM...

1. It is not a good idea to prefix the slip_no with the order_id in the db. Do this in the front end or use a view.

2. In SQL2000 you...

Sorry about the CROSS JOIN. I do try to suppress my urge to put all the join conditions in the WHERE clause!

Maybe:

SELECT SUM(S.ESDS) AS ESDSTotal

    ,COUNT(S.ESDS) AS ESDSCount

    ,COUNT(*) AS ESDSCountWithNulls

FROM DEMANDSPREAD S

    CROSS JOIN (

            SELECT MAX(S1.[TimeStamp]) AS MaxTimeStamp

            FROM DEMANDSPREAD S1

        ) D

WHERE S.[TimeStamp] BETWEEN DATEADD(minute, -1, D.MaxTimeStamp) AND D.MaxTimeStamp

And this will show all the paths:

;WITH LongestPath (PathID, TeamID, PrevTeamID, Stage, StageDuration, Total)

AS

(

    SELECT ROW_NUMBER() OVER (ORDER BY P.Total, P.Stage)

        ,P.TeamID, P.PrevTeamID, P.Stage, P.StageDuration, P.Total

    FROM #PathTotals P

    WHERE P.NextTeamID is NULL

    UNION ALL

    SELECT...

I have just had a chance to have a quick look at this again. The following bit of code may help in showing duplicate maximum duration paths:

-- Now show the...

What a nice question. I hope the following will help you.

I have taken a slightly different approach.

As I found your non-normalized dataset confusing, I normalized it as follows:

CREATE TABLE #Teams

(

    TeamID...

A rough outline of the logic is:

1. The temp table gives alls combinations of order and article with each row assigned a sequential number. (TID) (I do not know if...

A fast forward cursor may be quicker, but you could try using a temp table. Something like:

CREATE TABLE #temp

(

    TID int IDENTITY NOT NULL PRIMARY KEY

    ,IdOrder int NOT NULL

    ,IdArticle char(4) COLLATE...

Try using a derived table:

SELECT *

FROM TableA A

    JOIN TableB B

        ON A.APK = B.APK

    JOIN (

            SELECT B1.APK, MIN(B1.DateCol) AS DateCol

            FROM TableB B1

            GROUP BY B1.APK

        ) D

        ON B.APK = D.APK

            AND B.DateCol = D.DateCol

See which of the following is better for your data:

-- 1. Probably not very efficient due to join in sub-query

SELECT DISTINCT -- distinct may not be needed

    V.FISCAL_YEAR

    ,V.ACCOUNTING_PERIOD

    ,V.AMOUNT

    ,V.DESCR

    ,V.REPORT_ITEM

    ,V.REPORT_NAME

    ,'BUDGET' AS ACCOUNTING_TYPE

FROM View3...

I think you will need to have another user for reporting. This user will have:

1. read rights to the Crystal DB.

2. public rights to the Live DB with DENY SELECT...

Maybe:

UPDATE T

SET TreeOrder = D.TreeOrder

FROM YourTable T

    JOIN (

            SELECT T1.[ID]

                ,ROW_NUMBER( ) OVER (PARTITION BY T1.ParentID ORDER BY T1.[Name]) AS TreeOrder

            FROM YourTable T1

        ) D

        ON T.[ID] = D.[ID]