Solved

SELECT top 10 from MS Access

Posted on 2011-02-26
7
413 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
Technology Partners: 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!

 
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

Suggested Solutions

Title # Comments Views Activity
rebind a grid after user clicks on node in treeview 1 52
Time Conversions...both ways 2 32
Load data upon clicking a button 8 54
Set time on Session (ASP) 14 38
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…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

752 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