| Author |
 Topic  |
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
 Posted - 03 November 2001 : 23:50:14
|
OK, I'm having a big brain fade. Either I'm getting old or I'm just tired (OK, maybe a good bit of both), but I can't figure this out though I should be able to do it.
Here is what I have:
Name, Category1, LastModifiedDate, CreatedDate
Name, Category1, LastModifiedDate, CreatedDate
Name, Category1, LastModifiedDate, CreatedDate
Name, Category2, LastModifiedDate, CreatedDate
Name, Category2, LastModifiedDate, CreatedDate
Name, Category2, LastModifiedDate, CreatedDate
Name, Category2, LastModifiedDate, CreatedDate
Name, Category2, LastModifiedDate, CreatedDate
Name, Category2, LastModifiedDate, CreatedDate
Name, Category3, LastModifiedDate, CreatedDate
Name, Category3, LastModifiedDate, CreatedDate
What I was it to select the top 3 from EACH category, w/ top being the most recent according to the LastModifiedDate field. I would like to do it in one SQL statement so I can save myself some coding. I'm pretty sure I can do it, but I can't get it hammered out. Anyone have any clues where to start?
<separate rant>Is anyone else bugged by the fact that the SQL used in Access queries is different than the SQL you would use to access the same database in ASP? I was trying to use a like with a wildcard. In ASP, the wildcard is "%", but if you do it directly in Access, you have to use "*"......grrr. I took me a good ten minutes to figure out that one......
Dave Maxwell
--------------
Proud to be a "World Class" Knucklehead |
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 04 November 2001 : 00:06:40
|
I think you need two sql statements.
First to get all Categories from the DB. Put them in an array.
Then run Category Array in a For Next loop and execute the second sql in the loop with Where criteria matching the Category Array value.
GauravBhabu
There is only one miracle...That is LIFE! | It is difficult to IMPROVE on Perfection, There is no harm in Keep Trying. |
 |
|
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 04 November 2001 : 02:49:58
|
I hope I understand right, and if i do i think this is what you want...
strSql = "SELECT TOP 3 Name "
strSql = strSql & "FROM Category "
strSql = strSql & "ORDER BY LastModifiedDate ASC"
Brad
Edited by - redbrad0 on 04 November 2001 02:50:47 |
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 04 November 2001 : 03:19:42
|
strSql = "SELECT Category "
strSql = strSql & "FROM Category"
Set rsCats = conn.execute(strSql)
iCat = 0
if not rscats.eof then
do until rsCats.eof
if iCat = 0 then
ListCats = rsCats("Category")
else
ListCats = "#" & rsCats("Category")
end if
rsCats.movenext
loop
end if
rsCats.close
set rsCats = nothing
arrCats = split(ListCats, "#", -1, 1)
for iTop3 = lbound(arrCats) to ubound(arrCats)
strSql = "SELECT TOP 3 Name "
strSql = strSql & "FROM Category "
strSql = strSql & "WHERE Category= " & arrCats(iTop3)
strSql = strSql & "ORDER BY LastModifiedDate DESC"
set rsTop3 = conn.execute(strSql)
do until rsTop3.eof
Response.write rsTop3("Name") & "<br>"
rsTop3.movenext
loop
rsTop3.close
set rsTop3 = nothing
next
GauravBhabu
There is only one miracle...That is LIFE! | It is difficult to IMPROVE on Perfection, There is no harm in Keep Trying. |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 04 November 2001 : 04:45:54
|
If I've understood what your after correctly I think this might do the trick for you.
SELECT Name,Category,LastModDate
FROM MyTable p1
WHERE Name IN (SELECT TOP 3 Name FROM MyTable p2 WHERE p2.Category = p1.Category)
ORDER BY Category,LastModDate DESC
Halo of Xegony |
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 04 November 2001 : 05:07:09
|
That would be better.
GauravBhabu
There is only one miracle...That is LIFE! | It is difficult to IMPROVE on Perfection, There is no harm in Keep Trying. |
|
|
| |
Topic |
|
|
|
Topic Locked
