Search results for query: *

...INSERT @Dest SELECT '07', 'Personal numbers, messaging and mobiles' UNION ALL SELECT '077', 'Mobile phone' UNION ALL SELECT '07702', 'O2' SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY P.PhoneNumber ORDER BY LEN(D.Data) DESC) AS RowNum FROM @Phone P INNER JOIN @Dest...

Dang hit the wrong button, this version should work correctly:

Dang, forgot to test it before I sent it.. Scratch my entry as it doesn't work correctly. :)

For fun:

The ORDER BY will be applied, then the TOP ## will be applied. So, if I follow you, that is what you want and should work just fine. Did you try this already and not get the results you want?

I'd suggest getting a Split/Parse fuction and losing the Dynamic Sql, but I think this will work: sSQL = sSQL + 'WHERE tAGProjects.AGProjectKey IN (' + @AGProjectKey + ''')'

And for 2005 you can do: SELECT Golfer, AVG(Score) FROM ( SELECT A.Golfer, A.Score, ROW_NUMBER() OVER (PARTITION BY Golfer ORDER BY Score ASC) AS RowNum FROM @Golfers A ) AS Temp WHERE RowNum < 4 GROUP BY Golfer

Dang, nevermind.. I missed the part about two simultaneous inserts. I don't know if it'll help in your case, but this is a common problem with data warehousing. You might do some searches for how we do it there. The gist is that you have a "master" table that other systems get their IDs from.

...@Golfers VALUES (2,33) SELECT Golfer, AVG(Score) FROM ( SELECT A.Golfer, A.Score FROM @Golfers A INNER JOIN @Golfers B ON A.Golfer = B.Golfer WHERE A.Score >= B.Score GROUP BY A.Golfer, A.Score HAVING COUNT(*) BETWEEN 1 AND 3 ) AS T GROUP BY Golfer

jmille34, I'm not sure I fully understand your problem. Are you saying you have one server that you do an insert on and you can get the identity just fine, but when you replicate that table over to another server there are issues because that other server has the same table also with an...

Here is one way that should work:SELECT t1.unit, t1.dept, t1.code, t1.month, t1.year FROM tbl1 t1 INNER JOIN ( SELECT DISTINCT t2.unit, t2.dept, t2.code FROM tbl2 t2 WHERE t2.unit>'500' AND t2.dept>'600' ) AS Temp ON t1.unit = Temp.Unit AND t1.dept = Temp.dept AND...

Are you using SQL 2005? Also, can you give a sample of what your expected results should be from your sample data set? I'm confused if on which date difference you want.

You also may be able to use the SET TRANSACTION ISOLATION LEVEL command with the SERIALIZABLE option. Check out BOL (Books online) for moer information about isolation levels.

...SELECT 'x', 'b', 'n' ; -- Declare CTE WITH Temp AS ( SELECT c1, c2, c3, 0 AS Level FROM @T as T WHERE c3 = '0' UNION ALL SELECT T1.c1, T1.c2, T1.c3, Level + 1 FROM @T AS T1 INNER JOIN Temp AS T2 ON T1.c3 = T2.c2 ) -- Get results SELECT * FROM Temp

If you have 2005 you can use CROSS APPLY to acheive the result you are looking for. For example, I have a function that splits a list of integers: DECLARE @T TABLE (List VARCHAR(50)) INSERT @T SELECT '123,456,789' UNION ALL SELECT '987,654,321' SELECT Temp.Val FROM @T AS T CROSS APPLY...

LIKE '[a-z]%' LIKE '[0-9]%'

Inconceivable! Obligatory :)

Since you did not supply you desired output, I'm not sure what you want, but here is one option using UNION ALL as JBenson suggested:SELECT item_number, SUM(cases_scanned), SUM(cases_sold), SUM(units_sold), SUM(units_scanned) FROM ( SELECT uvb.item_number, COUNT(uvb.item_number)...

You need to alias your derived table:SELECT AdditionalStatus.LoanID, Max(AdditionalStatus.DateAdded) as DateAdded, AdditionalStatus.StatusValue FROM AdditionalStatus INNER JOIN ( SELECT Distinct LoanID FROM R_IFS_AdditionalStatus ) AS R_IFS_AdditionalStatus ON...