In 2004, I worked on a project where my boss...

Manie Verster (12/5/2008)

---

Smoking is my problem. I have tried numerous times already and after say 3 months I start again. . . . Give me some nice tips on...

Awesome! Having spent years using all sorts of languages to prepare cross-tabs, I really love the results here.

I, too, went back and read the discussion on the first article....

RE: audit table - it's not like a standard audit table. I have several of those, and the columns do match the datatypes of the tables that they audit....

Practically speaking, I expect only "American-keyboard" ascii characters, but I suspect the original designer wanted to cover international users.

But I can think of a number of other columns in this...

Lynn Pettis (11/25/2008)

---

This is an awesome site with many great people willing to share thier knowledge. I know I have picked up many new ideas here, and learned some...

No, actually, only 10,000 PersonIds in my test table of 5 million rows (1 million unique PersonIDs) have records that are out of order; that's about 1% of the ids....

AlexT (11/13/2008)

---

yes sorry, my mistake. if you don't mind you can try this

select a.PersonID, a.Version, a.DEDate,

case when b.PersonID is null then 0 else 1 end AS [Is ImProper]

from...

AlexT (11/13/2008)

---

that supposed to be faster, if clustered index applied on (PersonID, Version )

select distinct a.PersonID,a.Version, a.DEDate , case when b.PersonID is null then 0 else 1 end AS [Is...

AlexT (11/13/2008)

---

yes whatever,

select distinct a.PersonID,a.Version, a.DEDate , case when b.PersonID is null then 0 else 1 end AS [Is ImProper]

from PersonRecord a

left join (

select a.PersonID

from PersonRecord a

inner join...

Yes, you then need...

Peter M. (11/12/2008)

---

I'd be more interested in seeing the execution times where the data size is non-trivial. My suspicion would lead me to believe the row_number() solution might scale...

dfarran (11/13/2008)

---

It is also possible to do the query with just left joins,

SELECT a.PersonID, a.Version , a.dedate,

[is improper] = CASE WHEN MAX(c.version) IS...

I was curious how the different techniques performed, so I loaded up the PersonRecord table with more data using a Numbers table (please see article http://www.sqlservercentral.com/articles/TSQL/62867/ [/url]for Numbers table)

insert into...

Adam Haines (11/12/2008)

---

Good article!

This type of query can be handled much easier in SQL 2005, but I would still try to avoid a correlated subquery in SQL 2000. You...