In SQL2005, the best I can come up with for the sequence idea is a #temp table and two recursions. (As below)

If anyone can do any better I would be...

You may want something like the following. If not you will need to post test data and expected results in order for the problem to be understood.

DECLARE @t TABLE

(

    [Proc] varchar(10)...

ORDER BY

    CASE @SortBy

        WHEN 'S'

        THEN

            CASE

                WHEN column1 IS NULL AND column2 IS NULL

                THEN ''

                WHEN column1 IS NULL

                THEN SPACE(255) + CAST(column2 AS varchar(255))-- 255 or length column1

                WHEN column2 IS NULL

                THEN CAST(column1 AS varchar(255))

                ELSE...

ORDER BY

    CASE @SortBy

        WHEN 'S'

        THEN ISNULL(CAST(column1 AS VARCHAR(255)) + ',', '') + column2

        ELSE ISNULL(CAST(column3 AS VARCHAR(255)) + ',', '') + column2

    END

SELECT *

FROM InvoiceDetails D

    JOIN InvoiceSubDetails S

        ON D.[ID] = S.InvoiceDetailID

    JOIN (

            SELECT S1.InvoiceDetailID

                ,COUNT(*) AS SubCount

            FROM InvoiceSubDetails S1

            GROUP BY S1.InvoiceDetailID

        ) V

        ON D.[ID] = V.InvoiceDetailID

ORDER BY V.SubCount

SELECT

    CASE N.Number

        WHEN 1 THEN 'ParAmt'

        WHEN 2 THEN 'DenomQty'

        WHEN 3 THEN 'CInsNbr'

        WHEN 4 THEN 'HNbr'

    END AS Col

    ,CASE N.Number

        WHEN 1 THEN ParAmt -- cast to float if not already float

        WHEN 2 THEN...

SELECT

    CASE N.Number

        WHEN 1 THEN 'ParAmt'

        WHEN 2 THEN 'DenomQty'

        WHEN 3 THEN 'CInsNbr'

        WHEN 4 THEN 'HNbr'

    END AS Col

    ,CASE N.Number

        WHEN 1 THEN ParAmt -- cast to float if not already float

        WHEN 2 THEN...

It strikes me that we may have been seduced by our inner low level programmer. Maybe all that is required is a 256 row lookup table which...

I think the following is standard ANSI so it may work:

UPDATE FFIITMAP

SET FFIHGTH = (

        SELECT MAX(C.CFFIHGTH)

        FROM cubiscan C

        WHERE C.CFFIITMN = FFIITMAP.FFIITMN

        GROUP BY C.CFFIITMN

    )

WHERE EXISTS (

        SELECT *

        FROM cubiscan C1

        WHERE C1.CFFIITMN = FFIITMAP.FFIITMN

    )

He seems to want to reverse the bits in a tinyint and then show the number in Hex.

Maybe:

-- From http://www.cs.utk.edu/~vose/c-stuff/bithacks.html#ReverseByteWith64BitsDiv

DECLARE @big1 bigint

    ,@big2 bigint

SELECT @big1 = 8623620610

    ,@big2 = 1136090292240

-- result may...

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!