Select TOP 1 doesn't return the maximum date

[wooddsw]

I'm working on returning the most recent history record for a specific contact. I tried the MAX(CompletedDate) to no avail so I thought I'd try the TOP 1 method. When I change it to TOP 2 notice that the second row is a more recent date than the first row. Changing the query to TOP 1 returns the record for 20040326.

select top 2 convert(char,completeddate,112)as CompletedOn, completeddate, *
from history 
where contactid = 'CJPPIA00004A'
and type not in ('262156','262150','262151','262158','262160')
order by completedon

20040326 2004-03-26 20:00:00.000
20060821 2006-08-21 00:37:41.000

Anyone have a clue why the 2004 record is returning before the 2006 record? Any ideas would be greatly appreciated. I'm sure the solution is probably the reason the MAX() function is also not working.

 

[r937]

add DESC to the ORDER BY

r937.com | rudy.ca

 

[wooddsw]

Interesting....Adding DESC to the Order By clause returns 2 completely different records. However when changing the query to TOP 1 it did return what at this point appears to the the correct max date record.

Any further explanation of the internal execution process of the TOP function will be appreciated.

I'll only take so much on faith.

Thx

 

[gmmastros]

With Top 2... Were you expecting 2 completely similar records?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[wooddsw]

When looking at TOP 2 I was surprise to see that the result in TOP 1 (2004 date) wasn't the max date (2006 date). I really only want the TOP 1 result to be correct (2006 date)

 

[SQLBill]

You were getting the TOP date, just not the TOP that you expected. Look at this data.....

2001
2002
2003
2004
2005
2006

That is in ORDER BY asc, which is the default. TOP 1 from that gets 2001. The use of ORDER BY ... DESC turns the values into:

2006
2005
2004
etc.

Then TOP 1 from that gets 2006.

-SQLBill

Posting advice: FAQ481-4875

 

[gmmastros]

Imagine writing a query without TOP. View the results in Query Analyzer. Now add TOP 1. Only the first record that was previously returned will be returned with the TOP. TOP returns the first X number of records.

You can also use Top 10 percent to return the top 10 percent of the records. So... if your query originally returned 100 records (without any TOP specified), and you add top 10 percent to the query, then the first 10 records would be returned.

This is why the Order By is important. Without it, you get records that you are not expecting.

Does this make sense now?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

you are also converting the date and then ordering
if the results are wrong try

order by completeddate desc

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/