Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

SELECT top 10 from MS Access

I use the code below to select 10 newest articles:

strsql = "SELECT top 10 * FROM " & TABLE_NAME
if id > 0 then
    strsql = strsql & " WHERE " & COL_ID & "=" & id
    strsql = strsql & " AND Article_start_date <= now() "
else
    strsql = strsql & " WHERE Article_start_date <= now() "
end if

strSql = strSql & " ORDER BY [Article_ID] desc;"
rs.open strsql, conn, 2, 2

I wan't to order the 10 newest articles by Article_Hits so the most readed article will be listed first.

When I change:

ORDER BY [Article_ID] desc;"

to:
ORDER BY [Article_ID] desc, [Article_Hits] desc;"

Then I get some very old articles, not the 10 newest.

How can I get the 10 newest aricles and only orderby Article_Hits on that same 10 articles?
0
GDB08
Asked:
GDB08
  • 3
  • 2
  • 2
1 Solution
 
sammySeltzerCommented:
how are articles stored on your db, by start_date?

If so, then

group by start_date

Then ORder Article_Hits desc
0
 
sammySeltzerCommented:
Having thought about it some more. you probably  need to group by Article_Hits

Then order by article_id desc
0
 
GDB08Author Commented:
Thanks for the reply.

I get an error on this:
strSql = strSql & " GROUP BY [Article_Hits];"
strSql = strSql & " ORDER BY [Article_ID] desc;"

Is there something wrong with the code?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sammySeltzerCommented:
please show the error.

If you get the error on GROUP BY... then it means that you do one of two things.

1,  you can either SELECT the fieldnames you want to desplay or

Add ALL the fieldnames to the GROUP BY clause.

I think that's where the error is coming from.
0
 
clockwatcherCommented:
You should be able to use a subquery to get what you're after... e.g.,

  select * from (select top 10 * from table where article_start_date <= now() order by article_start_date desc) order by article_hits desc

The inner subquery will return your top 10 newest.  The outer query will then sort those results by hits.  Which field determines how new an article is?  If it's article_start_date then try:

strsql = "SELECT top 10 * FROM " & TABLE_NAME
if id > 0 then
    strsql = strsql & " WHERE " & COL_ID & "=" & id
    strsql = strsql & " AND Article_start_date <= now() "
else
    strsql = strsql & " WHERE Article_start_date <= now() "
end if

strSql = strSql & " ORDER BY [Article_start_date] desc"

strSql = "select * from (" & strSQL & ") order by article_hits desc"
0
 
GDB08Author Commented:
Thanks for the reply.

This doesn't work, I still get the first 10 articles in start date order, not ordered by article hits.
0
 
clockwatcherCommented:
I'm not sure where you're running into problems but the subquery will work fine.  Attached is an example.  The database itself also includes an example query stored in it that demonstrates that it works without problem.

   example.txt example.mdb
<%
option explicit

dim rs

set rs = server.createobject("ADODB.Recordset")

rs.open "select * from (select top 3 * from articles order by article_start_date desc) order by article_hits desc", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath(".\example.mdb")

response.write "<table border=1><tr><td>Hits</td><td>Start Date</td><td>Description</td></tr>"
do while not rs.eof

  response.write "<tr><td>" & rs("article_hits") & "</td><td>" & rs("article_start_date") & "</td><td>" & rs("article_desc") & "</td></tr>"

  rs.movenext

loop

rs.close
set rs = nothing
%>

Open in new window

0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now