Hi Pop

SELECT a.casenum, a.audit_date, c.audit_date as StartDate, a.stepname

...

So you want the cases having their most recent step in your list of stepnames? Sorry to be a pain!

Cool, many thanks Pop.

Got a question for you:

SELECT ...

Oh all right then. No funny stuff either.

DECLARE @ItemID INT

DECLARE @FirstCol varChar(5), @SecondCol INT, @ThirdCol Char(1)

SET @ItemID = 1

UPDATE #Testing SET ThirdCol = CASE @ItemID WHEN 1 THEN 'D'...

Hi Tim

I use the ASCII function for this, usually the first two characters of the column...

DECLARE @tempvar VARCHAR(5)

SET @tempvar = CHAR(10) + CHAR(13)

SELECT ASCII(LEFT(@tempvar,1)), ASCII(SUBSTRING(@tempvar,2,1))

Cheers

ChrisM

Shaun McGuile (7/1/2008)

---

the point of the exercise -

...so all that is left is a table full of records which were duplicated at least once!

Misleading title though....;)

Jeff, Chris; It falls...

Read the OP's second...

Jeff Moden (7/1/2008)

---

You guys are missing the point of this particular exercise... despite the name of the post, this is NOT about the elimination of duplicates. Here's what the...

Pop

Your sample data doesn't return any rows. This chunk...

SELECT ...

mlando.mngomezulu (7/1/2008)

---

Actually the first step makes sure that the temptable only have one record per corresponding duplicate records. This is achieved by the "group by" clause. This is grouped...

mlando.mngomezulu (7/1/2008)

---

Step1: You need to create a table containing only the duplicate records by first selecting all the duplicate records into a new table i.e.

Insert into temptable...

Nicking Barry's excellent idea (and Janines test data), here's another way which is quite fast:

DROP TABLE #Testing

CREATE TABLE #Testing (

FirstCol varChar(5),

...

Ahah that explains...

Mark (6/24/2008)

---

Another alternative

SELECT

A.RetailerID,

X.RetailerIDTheirs

FROM TableA AS A

CROSS APPLY (SELECT TOP 1 B.RetailerIDTheirs FROM TableB B...

Hi Simon

This is simple but somewhat restrictive:

SELECT A.RetailerID, MIN(B.RetailerIDTheirs) AS RetailerIDTheirs

FROM #TableA A

LEFT JOIN #TableB B ON B.RetailerID = A.RetailerID

GROUP BY A.RetailerID

This is less simple but offers more flexibility:

SELECT...