Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SELECT top 10 from MS Access

Posted on 2011-02-26
7
Medium Priority
?
435 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

722 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