-- A numeric column will sort numerically.

-- This column is character and will sort alphabetically

-- so you have to work around it. Here's one way.

;with cte as

(

select '1' as...

Don. (10/12/2012)

---

Essentially I have a Contact table and a History table.

I'd like to get the join the tables, but only show the most recent record in History.

How would I join...

Cadavre (10/12/2012)

---

That the one on Broad Street? I'm in the process of moving house, we...

Here are the timings from a slender 1 million row Periods table, with an ordinary index on startdate:

========== CADAVRE ==========

Table 'Worktable'. Scan count 1, logical reads 254686, physical reads 0,...

Cadavre (10/12/2012)

---

OK, I'm back in the office 🙂

...

Looking at the results suggests to me that I did something wrong in the testing 😀

If not though, then the huge difference is...

Cadavre (10/12/2012)

---

I'm not back yet, but will be leaving soon as it looks like they're nearly finished so can test in 30 minutes or so. But could your code not...

mamzy.rahman (10/12/2012)

---

hi guys basically iv got a table here with a field called aims and a field called modules. i need to run a query to show if one aim...

nitin.varshney (10/12/2012)

---

Hi

Please tell me which is better for the performance impact ?

1. Insert into #temp table Using the cross join with tables.

2. Insert into #temp table using the while...

Cadavre (10/12/2012)

---

I'll check when I get back from lunch. My gut feeling is that mine will be heavier on reads whilst yours is heavier on scans.

Not a clue about...

Cadavre (10/12/2012)

---

I'm on my lunch waiting in the car for something, so this is untested and coded together using the text editor on my phone.

Would this work for...

This is quite a bit different to Craig's, it would be interesting to discover the performance of them both:

SELECT s.*,

ID= CASE WHEN x.lag < y.lag THEN x.ID ELSE y.ID...

Can you post the code, Mahesh? Also, if you can, the actual execution plans?

Hello

You are likely to get a much faster response if you provide DDL for your sample data. Here's your 'master' table,...

Here's a guess based upon the information folks have already extracted from the OP:

--test ddl

DROP TABLE #sales_log

CREATE TABLE #sales_log (member_id varchar(50),company_id varchar(50),purchase_dt datetime,purchase_amt money)

insert into #sales_log(member_id,company_id,purchase_dt,purchase_amt)

select 'Jen9921', '222141','1/2/2011','11.99' union...

Yes, in a limited sense. Check out Plan Guides in BOL.