Official TWOTY 2011 question thread

---

Posted by LIWeddings NYCityWeddings

SELECT TOP 21 P.ID AS PostID, T.Subject AS TopicTitle, P.SID AS TopicPostNumber, M.Username, P.Posted, P.Message
FROM (
SELECT TOP 9000010 ID FROM Posts ORDER BY ID
) DERIVEDTBL
INNER JOIN Posts P ON P.ID = DERIVEDTBL.ID
INNER JOIN Topics T ON T.ID = P.Topic
INNER JOIN Members M ON M.ID = P.UserID
ORDER BY P.ID DESC

---

would be faster to do:

SELECT P.ID AS PostID, T.Subject AS TopicTitle, P.SID AS TopicPostNumber, M.Username, P.Posted, P.Message
FROM (SELECT TOP 21 P.ID AS PostID
FROM ( SELECT TOP 9000010 ID FROM Posts ORDER BY ID) DERIVEDTBL) as Top21Posts
INNER JOIN Posts P ON P.ID = Top21Posts.ID
INNER JOIN Topics T ON T.ID = P.Topic
INNER JOIN Members M ON M.ID = P.UserID
ORDER BY P.ID DESC

---

Posted by VCanizalez

I HAVE NO IDEA WHAT LANGUAGE IS THAT!!!!!!!!!

---

it's SQL (pronounced sequel)

---

Posted by AwaitingAugust

---

Posted by LIWeddings NYCityWeddings

SELECT TOP 21 P.ID AS PostID, T.Subject AS TopicTitle, P.SID AS TopicPostNumber, M.Username, P.Posted, P.Message
FROM (
SELECT TOP 9000010 ID FROM Posts ORDER BY ID
) DERIVEDTBL
INNER JOIN Posts P ON P.ID = DERIVEDTBL.ID
INNER JOIN Topics T ON T.ID = P.Topic
INNER JOIN Members M ON M.ID = P.UserID
ORDER BY P.ID DESC

---

would be faster to do:

SELECT P.ID AS PostID, T.Subject AS TopicTitle, P.SID AS TopicPostNumber, M.Username, P.Posted, P.Message
FROM (SELECT TOP 21 P.ID AS PostID
FROM ( SELECT TOP 9000010 ID FROM Posts ORDER BY ID) DERIVEDTBL) as Top21Posts
INNER JOIN Posts P ON P.ID = Top21Posts.ID
INNER JOIN Topics T ON T.ID = P.Topic
INNER JOIN Members M ON M.ID = P.UserID
ORDER BY P.ID DESC

---

yeah but i wrote that 8 million posts ago when i was green to sql.

---

Posted by LIWeddings NYCityWeddings

---

Posted by atitus724

---

Posted by LIWeddings NYCityWeddings

---

Posted by AwaitingAugust

---

Posted by LIWeddings NYCityWeddings

---

Posted by dianeb888

What about the 9,000,000 post??

---

still have to run the program for that.
it's CPU intensive and with everyone on stalking TWOTY posts I'm going to run that script later today or tonight

---

i'll fix it for you if you send it over :-P

---

this will blow some minds!

SELECT TOP 21 P.ID AS PostID, T.Subject AS TopicTitle, P.SID AS TopicPostNumber, M.Username, P.Posted, P.Message
FROM (
SELECT TOP 9000010 ID FROM Posts ORDER BY ID
) DERIVEDTBL
INNER JOIN Posts P ON P.ID = DERIVEDTBL.ID
INNER JOIN Topics T ON T.ID = P.Topic
INNER JOIN Members M ON M.ID = P.UserID
ORDER BY P.ID DESC

---

whattttttttttttt

---

she asked for the programming to give me the 9 millionth post
well that's what this does.
it also gives me the 10 posts before and after the 9 millionth
see how easy this is?

---

ouch my brain

---

Posted by LIWeddings NYCityWeddings
yeah but i wrote that 8 million posts ago when i was green to sql.

---

haha - it's ok - i forgive you we all make our rookie mistakes

---

Posted by AwaitingAugust

---

Posted by LIWeddings NYCityWeddings
yeah but i wrote that 8 million posts ago when i was green to sql.

---

haha - it's ok - i forgive you we all make our rookie mistakes

---

still wasn't bad for a newbie who only read the book!

---

Posted by LIWeddings NYCityWeddings

---

Posted by AwaitingAugust

---

Posted by LIWeddings NYCityWeddings
yeah but i wrote that 8 million posts ago when i was green to sql.

---

haha - it's ok - i forgive you we all make our rookie mistakes

---

still wasn't bad for a newbie who only read the book!

---

no, not bad at all!

so semi-random question (which i feel like i should already know, but i don't) - do you do all the programming for this site, or is it somebody else?

ps: i think i kind of highjacked your thread .... sorry!

---

Posted by AwaitingAugust

ps: i think i kind of highjacked your thread .... sorry!

---

don't ever let it happen again!

---

Posted by AwaitingAugust

ps: i think i kind of highjacked your thread .... sorry!

---

so for the heck of it all I tried your script.
2 errors!

who's the newbie now!!!!!

---

Posted by LIWeddings NYCityWeddings

---

Posted by AwaitingAugust

ps: i think i kind of highjacked your thread .... sorry!

---

don't ever let it happen again!

---

no Sir, never again!

---

Posted by LIWeddings NYCityWeddings

---

Posted by AwaitingAugust

ps: i think i kind of highjacked your thread .... sorry!

---

so for the heck of it all I tried your script.
2 errors!

who's the newbie now!!!!!

---

t'was something silly (not updating the alias) ... but you knew that already

try this one:

SELECT P.ID AS PostID, T.Subject AS TopicTitle, P.SID AS TopicPostNumber, M.Username, P.Posted, P.Message
FROM
( SELECT TOP 21 DERIVEDTBL.ID AS PostID FROM
( SELECT TOP 9000010 ID FROM Posts ORDER BY ID) DERIVEDTBL
) as Top21Posts
INNER JOIN Posts P ON P.ID = Top21Posts.PostID
INNER JOIN Topics T ON T.ID = P.Topic
INNER JOIN Members M ON M.ID = P.UserID
ORDER BY P.ID DESC

---

Posted by AwaitingAugust

---

Posted by LIWeddings NYCityWeddings

---

Posted by AwaitingAugust

ps: i think i kind of highjacked your thread .... sorry!

---

so for the heck of it all I tried your script.
2 errors!

who's the newbie now!!!!!

---

t'was something silly (not updating the alias) ... but you knew that already

try this one:

SELECT P.ID AS PostID, T.Subject AS TopicTitle, P.SID AS TopicPostNumber, M.Username, P.Posted, P.Message
FROM
( SELECT TOP 21 DERIVEDTBL.ID AS PostID FROM
( SELECT TOP 9000010 ID FROM Posts ORDER BY ID) DERIVEDTBL
) as Top21Posts
INNER JOIN Posts P ON P.ID = Top21Posts.PostID
INNER JOIN Topics T ON T.ID = P.Topic
INNER JOIN Members M ON M.ID = P.UserID
ORDER BY P.ID DESC

---

first script errors

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier 'P.ID' could not be bound.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'ID'.

---

Posted by LIWeddings NYCityWeddings

---

Posted by AwaitingAugust

---

Posted by LIWeddings NYCityWeddings

SELECT TOP 21 P.ID AS PostID, T.Subject AS TopicTitle, P.SID AS TopicPostNumber, M.Username, P.Posted, P.Message
FROM (
SELECT TOP 9000010 ID FROM Posts ORDER BY ID
) DERIVEDTBL
INNER JOIN Posts P ON P.ID = DERIVEDTBL.ID
INNER JOIN Topics T ON T.ID = P.Topic
INNER JOIN Members M ON M.ID = P.UserID
ORDER BY P.ID DESC

---

would be faster to do:

SELECT P.ID AS PostID, T.Subject AS TopicTitle, P.SID AS TopicPostNumber, M.Username, P.Posted, P.Message
FROM (SELECT TOP 21 P.ID AS PostID
FROM ( SELECT TOP 9000010 ID FROM Posts ORDER BY ID) DERIVEDTBL) as Top21Posts
INNER JOIN Posts P ON P.ID = Top21Posts.ID
INNER JOIN Topics T ON T.ID = P.Topic
INNER JOIN Members M ON M.ID = P.UserID
ORDER BY P.ID DESC

---

yeah but i wrote that 8 million posts ago when i was green to sql.

---

um.. WT F is that!? i totally skipped to the last page.

---

Posted by AwaitingAugust

---

Posted by LIWeddings NYCityWeddings

---

Posted by AwaitingAugust

ps: i think i kind of highjacked your thread .... sorry!

---

so for the heck of it all I tried your script.
2 errors!

who's the newbie now!!!!!

---

t'was something silly (not updating the alias) ... but you knew that already

try this one:

SELECT P.ID AS PostID, T.Subject AS TopicTitle, P.SID AS TopicPostNumber, M.Username, P.Posted, P.Message
FROM
( SELECT TOP 21 DERIVEDTBL.ID AS PostID FROM
( SELECT TOP 9000010 ID FROM Posts ORDER BY ID) DERIVEDTBL
) as Top21Posts
INNER JOIN Posts P ON P.ID = Top21Posts.PostID
INNER JOIN Topics T ON T.ID = P.Topic
INNER JOIN Members M ON M.ID = P.UserID
ORDER BY P.ID DESC

---

first script errors

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier 'P.ID' could not be bound.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'ID'.

---

Posted by LIWeddings NYCityWeddings
first script errors

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier 'P.ID' could not be bound.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'ID'.

---

yep, that's what i fixed

does the 2nd one work?

okay, no more SQL scripts!

back to TWOTY

Who wants to go?

---

Posted by Im_My_Own_Princess

um.. WT F is that!? i totally skipped to the last page.

---

lol sorry Megan (and everybody else) - it's the code for getting the 9 millionth post

---

Posted by AwaitingAugust

---

Posted by LIWeddings NYCityWeddings
first script errors

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier 'P.ID' could not be bound.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'ID'.

---

yep, that's what i fixed

does the 2nd one work?

---

Dunno - gotta log back in and I have to leave for an appointment in 10 minutes so I'll give it a crack later