Solved

SELECT top 10 from MS Access

Posted on 2011-02-26
7
423 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 29

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 29

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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 29

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

Industry Leaders: 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!

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

623 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