Latest 2 days but not restricted 2 days from now 1

[Karl Blessing]

I have this query (which is called from ASP)

RS.Open "Select PostDate, PostTopic, Post, Signature from Postings order by PostDate DESC", Conn, 1, 3

I want to change that query to only pull back the latest 2 days of records, I tried to do latest records 2 days from now, but that didnt return anything, so I want to return least something so I am wondering how to return the latest 2 days of postings from the recordset. Karl Blessing aka kb244{fastHACK}

 

[redlam]

I think this is what you want:
Select Top 2
PostDate,
PostTopic,
Post,
Signature
from Postings
order by PostDate DESC

 

[Karl Blessing]

Not exactly what I meant (though that does help in the future when .maxrecords doesnt work)

What I meant, is the all the post in the latest 2 days. not just 2 posts, say the latest two days are

6/18 and 6/19 , I want all postings from those days to come back

but if it's 6/30, I still want it to bring back 6/19 ans 6/18 cuz those are the latest two days in the recordset. (assuming no entries were placed there after) Karl Blessing aka kb244{fastHACK}

 

[redlam]

Ok, Top 2 definitely wouldn't work for that. I would try something like this:

Select
PostDate,
PostTopic,
Post,
Signature
from Postings
where PostDate in (Select Distinct Top 2 PostDate From Postings order by PostDate DESC)
order by
PostDate desc

 

[Karl Blessing]

Hmm I was wondering if there was an example with DateAdd or DateDiff , but yea I can see how that works, especially with the nested select Karl Blessing aka kb244{fastHACK}

 

[Karl Blessing]

hmm I get this when I try your implementation.

<!-- -2147217900 / Syntax error in FROM clause. / Microsoft JET Database Engine --> Karl Blessing aka kb244{fastHACK}

 

[Karl Blessing]

RS.Open &quot;Select PostDate, PostTopic, Post, Signature from Postings where PostDate in (Select Distinct Top 2 PostDate From Postings order by PostDate DESC) order by PostDate DESC&quot;, Conn, 1, 3

so you can see the exact string I'm using in case I missed something Karl Blessing aka kb244{fastHACK}

 

[redlam]

I'm not sure DateDiff would work unless you consistently have data where DateDiff(dd,PostDate,GetDate()) < 2... although maybe I'm just not being creative enough.

 

[redlam]

Hmmm.... odd error - well, odd for me anyway. I'm not familiar with ASP. The query works OK using Query Analyzer. I'm checking into other things... back in a min.

 

[redlam]

Sorry, I've only had minimal experience using ADO and no experience using JET. Thought I could pick a few brains for you here but no one is familiar. Maybe try posting that error in the ASP forum?

 

[Karl Blessing]

Yea, I am basically using a local Access Database.

Thanks for the try though, I might have to just resort to a number of posts, until I Can figure it out, least you helped me with that, since maxrecords wouldnt work.

-Thanks

(goto

http://www.kb244.com

to see the postings that I was talking about, also I got a beta design going at

http://www.kb244.com/kbbeta/)

Karl Blessing aka kb244{fastHACK}

 

[redlam]

Hmmm... I tested the query in Access and it works OK there too. Are you sure you have the correct table name in the From clause? Not sure how you're using Access... whether you have local tables or if you are linking them from SQL Server but if they are linked, Access usually adds the 'dbo_' prefix to the table names. I'm wondering if your table Postings is really dbo_Postings in Access - could that be the problem?
Just a stab...

 

[Karl Blessing]

code between kb244.com and kb244.com/kbbeta are identical when it comes to opening the connection.

also SiteOLE ( I put my connection strings into a *.inc file and pull them later, it's a way of having my very own local DSN hehe, dont wana pay them 1$ extra for each DSN I need)

SiteOLE = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\

http://WWWRoot\kb244com\www\_private\database\SiteDb2k.mdb;Mode=ReadWrite;Persist

Security Info=False&quot;

current code on home.asp of kb244.com works just fine

Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.Recordset")

Conn.Open SiteOLE, "", ""
RS.Open "Select * from Postings order by PostDate DESC", Conn, 1, 3

After (tested on /kbbeta) doesnt work

Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.Recordset")
Conn.Open SiteOLE, "", ""
RS.Open "Select PostDate, PostTopic, Post, Signature from Postings where PostDate in (Select Distinct Top 2 PostDate From Postings order by PostDate DESC) order by PostDate DESC", Conn, 1, 3

also just a note.
Select PostDate, PostTopic, Post, Signature from Postings order by PostDate DESC

works just fine too, so I know the error is refering to the nested select statement. Karl Blessing aka kb244{fastHACK}

 

[Vancouverite]

This is an Access/Jet query, not a SQL Server query? Try this:

Select PostDate, PostTopic, Post, Signature from Postings where PostDate >= (Select Dateadd(&quot;d&quot;, -1, Max(PostDate)) From Postings) order by PostDate DESC

 

[Karl Blessing]

Yep that works just great, I modified it to be -2 to include two days worth, but when I seen only one post come back I was confused, but then I realized its because the latest is 6/12, and the only day before that is 6/6.

Thanks alot. I'll probally modify this so that I can either do it based on number of post (if the days count is too small or too big) etc. well I'll figure something out, you provided me with the ways on how to do it both ways.

thanks. Karl Blessing aka kb244{fastHACK}