Why are you pulling results from your two source tables into table variables before using them in your final query? This is a shedload of extra code (trivial) and could...

chris.king (9/28/2010)

---

Chris Morris-439714 (9/28/2010)

---

If the seven foreign key fields from any row of Table A reference seven different rows in Table B, then yes you will need to join Table...

If the seven foreign key fields from any row of Table A reference seven different rows in Table B, then yes you will need to join Table B seven times...

Jaya, I reckon the most cost-effective way to approach your problem at this point in time is to see how each of your queries performs when you sub in the...

Alvin Ramard (9/27/2010)

---

Since you said...

Brandie Tarvin (9/27/2010)

---

Kit G (9/27/2010)

---

The part makes my head hurt is the joining the table to itself. Know of any articles about that which might help make my head...

Check that this is indeed the case. Using 2k8R1, the outer query returns 20 rows as if...

krishnakumar-293737 (9/27/2010)

---

Select top 20 * from Database1..MY_TABLE (nolock)

Where THE_ID = (Select THE_ID from Database2..MY_TABLE_2 Where OTHER_FIELD = 'VALUE')

Order By MY_COL desc

Now....the subquery is invalid, there is no column THE_ID on...

steveb. (9/27/2010)

---

I thought the issue is that the column does not exist in the table in the sub-query. Not that the sub-query returns mulitple rows.

It definetly throws an...

Brandie Tarvin (9/27/2010)

---

Chris, your code doesn't do what he wants Menu1 and Menu2 to do.

Also, you forgot to remove the # sign from your table name. Since he's a newbie...

hugh.hemington (9/26/2010)

---

...I'm trying to apply this to a situation where I need the first duplicate record, but I need to return the name and address, while partitioning only on the...

Fat freakin' Freddy's Cat.

Hiber-freakin'-nate

Is there a CTE in here anywhere?

If there is, then this article is probably worth a read:

http://www.kbalertz.com/2322209/Incorrect-results-parentheses-around-SELECT-statement-common-table-expression-Server.aspx

Edit: found the link.

And another one:

;WITH OrderedPartitionedData AS

(SELECT RowID = ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY EventDateTime), *

FROM #DataTest)

SELECT dy1.venue, dy1.address1, dy1.city, dy1.[state], dy1.zip,

EDate1 = CONVERT(VARCHAR, dy1.eventdatetime, 101),

Edate2...