OK, I'll play. . .

Your code:

SET STATISTICS IO ON

SET STATISTICS TIME ON

;WITH t1(N) AS (SELECT 1 UNION ALL SELECT 1),

t2(N) AS...

anthony.green (2/24/2012)

---

seems to be a problem with the chars putting in &#x0D

USE [master]; SELECT DatabaseName = DB_NAME(), CollectionD

Sure I can figure it out, time to brush up on...

Duplicate post.

Please don't post your question twice, it fragments replies and makes it much more difficult for the unpaid volunteers of this site to help.

Hello and welcome to SSC!

I'd like to be able to help, but it seems you've forgot to...

Hello and welcome to SSC!

I'd like to be able to help, but it seems you've forgot to post readily consumable sample data and ddl scripts.

If you could read this article[/url]...

Wow. . . OK.

This will generate your 11,881,376 (!!) combinations

SELECT a.alpha+b.alpha+c.alpha+d.alpha+e.alpha

FROM (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',N,1) AS alpha

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

...

sp_msforeachdb is known to miss databases and has problems with names of databases (e.g. call you database "I am a [bracket]" then see what happens to sp_msforeachdb)

This is howone way...

The code would work if the table exists, it only fails because...

Add the INSERT part into dynamic SQL then execute it from that.

It won't work otherwise, because if the table doesn't exist then the code fails before executing.

HowardW (2/23/2012)

---

Is anyone here going to SQLBits X in London?

Looking forward to some of the BI sessions as I haven't spent enough time delving into 2012 yet.

Are there...

DECLARE @start INT = 22, @duration INT = 5;

WITH t1(N) AS (SELECT 1 UNION ALL SELECT 1),

t2(N) AS (SELECT 1 FROM t1 x, t1 y),

t3(N) AS (SELECT 1 FROM t2...

SELECT a.roles AS 'PEOPLE/Person/@role', a.personrole AS 'PEOPLE/Person', a.NAME AS 'PEOPLE/Name'

FROM (VALUES ('SUPPLIER', 'Another Thing', 'Some Name1'), ('Buyer', 'Another Thing2', 'Some Name2')) a(roles, personrole, NAME)

FOR XML path('')

Returns

<PEOPLE>

<Person role="SUPPLIER">Another Thing</Person>

...

david.alcock (2/22/2012)

---

No its OK, I left the element in above the attribute line, removing that and hey presto! End tag included. Thank you ever so much, massively appreciated.

However,...

You haven't said how you determine which 5 to keep. In your sample data, "178976" has 6 "po_Number".

Assuming you're using SQL Server 2000, here's one way to do it:...

BEGIN TRAN

--Sample data

SELECT X,Y

INTO yourTable

FROM (VALUES('A', 'B'),('A', 'B'),('A', 'C'),('A', 'C'),('A', 'C'))a(X,Y)

--Solution

SELECT X,Y,

ROW_NUMBER() OVER(PARTITION BY X,Y ORDER BY X,Y) AS SEQ

FROM yourTable

ROLLBACK