Solved

SELECT top 10 from MS Access

Posted on 2011-02-26
7
408 Views
Last Modified: 2012-05-11
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
Comment
Question by:GDB08
  • 3
  • 2
  • 2
7 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 34988221
how are articles stored on your db, by start_date?

If so, then

group by start_date

Then ORder Article_Hits desc
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 34988232
Having thought about it some more. you probably  need to group by Article_Hits

Then order by article_id desc
0
 

Author Comment

by:GDB08
ID: 34988313
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 28

Expert Comment

by:sammySeltzer
ID: 34988847
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
 
LVL 25

Expert Comment

by:clockwatcher
ID: 34994554
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
 

Author Comment

by:GDB08
ID: 34995141
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
 
LVL 25

Accepted Solution

by:
clockwatcher earned 500 total points
ID: 34999806
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Migrated SQL connection issues 2012 6 39
Replacing smart quotes from word 7 67
C# page opens in new window 3 55
HTML in email body has extra  tick marks 3 62
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now