?
Solved

ORDER BY Subquery result under access SQL

Posted on 2008-02-09
8
Medium Priority
?
985 Views
Last Modified: 2011-10-19
The question is very easy, I need to order the results by Lastdate. Lastdate being a subquery result, access doesn't like it on the ORDER BY clause, I have tried ORDER BY 1 DESC but access seems to laught at me...
SELECT (SELECT TOP 1 fld_fechaPst FROM tbl_ForumPosts as fp WHERE fp.fk_ParentPst=PP.pk_numeroPst ORDER BY fld_fechaPst DESC) as LastDate, *
 FROM tbl_ForumPosts AS PP,tbl_users WHERE pk_numeroUsr=fk_autorPst AND fk_ParentPst=0 AND fk_Foro=@idforo 
 ORDER BY fld_Sticky, LastDate DESC

Open in new window

0
Comment
Question by:Drakecoldwinter
  • 4
  • 3
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20857032
please try this:
SELECT (SELECT TOP 1 fld_fechaPst FROM tbl_ForumPosts as fp WHERE fp.fk_ParentPst=PP.pk_numeroPst ORDER BY fld_fechaPst DESC) as LastDate, *
 FROM tbl_ForumPosts AS PP,tbl_users WHERE pk_numeroUsr=fk_autorPst AND fk_ParentPst=0 AND fk_Foro=@idforo 
 ORDER BY fld_Sticky, 1 DESC 
resp: 
SELECT (SELECT TOP 1 fld_fechaPst FROM tbl_ForumPosts as fp WHERE fp.fk_ParentPst=PP.pk_numeroPst ORDER BY fld_fechaPst DESC) as LastDate, *
 FROM tbl_ForumPosts AS PP,tbl_users WHERE pk_numeroUsr=fk_autorPst AND fk_ParentPst=0 AND fk_Foro=@idforo 
 ORDER BY fld_Sticky, (SELECT TOP 1 fld_fechaPst FROM tbl_ForumPosts as fp WHERE fp.fk_ParentPst=PP.pk_numeroPst ORDER BY fld_fechaPst DESC) DESC

Open in new window

0
 
LVL 2

Author Comment

by:Drakecoldwinter
ID: 20857220
it gives me a syntax error in ORDEN BY clause
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20857248
@Drakecoldwinter: Do both the queries posted by Angel throw an error? I tried a version of the first one and it worked for me. What error do u get?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
LVL 2

Author Comment

by:Drakecoldwinter
ID: 20859517
I'm sorry,
The first query doesn't do anything (as I sayed in my original description, ORDER BY 1 doesn't seem to work at all). The second query gives me an order by syntax error.

I must note that I'm using Microsoft access 2003. Also if I use the wizard of access and ask it to sort by lastDate it produces a query like the second query proposed by angel (which gives a syntax error and doesn't work)

If I can give more details the query is used in a forum, I have a single table with all the posts and I use a field fk_parent to know which fields are under which posts, now I need the post to go up on the list if they receive a posts. Below is the results from ORDER BY 1 query, as you can see "Consejos para nuevos moteros" and "Concurso" should be at the bottom of the list while "Saludos en V'ssss" should be the first one... :( I added a test database with data inside if you wish to test. Just delete .doc from the file.
LastDate	           fld_fechaPst	          fld_TitlePst
06/02/2008 10:13:23 PM	03/02/2008 3:25:29 PM	Chicas Emporio Malote
01/02/2008 9:41:12 PM	30/01/2008 10:30:01 AM	Seguros a todo riesgo
	                28/01/2008 4:53:38 PM	Consejos para nuevos moteros
27/01/2008 10:27:06 PM	27/01/2008 9:51:58 PM	Vivan los paquetes !!
27/01/2008 9:39:33 PM	27/01/2008 6:42:24 PM	Saludos
27/01/2008 6:41:07 PM	26/01/2008 11:08:18 AM	Las revistas: Cuentan la verdad?
09/02/2008 7:19:17 PM	25/01/2008 12:58:56 PM	Saludos en V'sss
	                24/01/2008 12:10:23 PM	Concurso Portalmotos

Open in new window

test.mdb.doc
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1400 total points
ID: 20860423
here we go, let's use the DMax() function:
SELECT DMax("fld_fechaPst","tbl_ForumPosts","fk_ParentPst=" & [PP].[pk_numeroPst]) AS LastDate, *
FROM tbl_ForumPosts AS PP
WHERE (((PP.fk_ParentPst)=0) AND ((PP.fk_foro)=3))
ORDER BY DMax("fld_fechaPst","tbl_ForumPosts","fk_ParentPst=" & [PP].[pk_numeroPst]) DESC , PP.fld_Sticky;

Open in new window

0
 
LVL 2

Author Closing Comment

by:Drakecoldwinter
ID: 31429447
thank a lot
0
 
LVL 2

Author Comment

by:Drakecoldwinter
ID: 20860724
That works very well, I guess DMax is an access function only and that under MS SQL I will need to use MAX or MIN functions or something else. As I'm not migrating yet this solution will be perfect until I get MSSQL later on the run. Thanks a lot.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20860837
>I guess DMax is an access function only
yes.

>and that under MS SQL I will need to use MAX or MIN functions o
in ms sql server:



SELECT sub_alias.*
FROM (
SELECT (SELECT TOP 1 fld_fechaPst FROM tbl_ForumPosts as fp WHERE fp.fk_ParentPst=PP.pk_numeroPst ORDER BY fld_fechaPst DESC) as LastDate, *
 FROM tbl_ForumPosts AS PP,tbl_users WHERE pk_numeroUsr=fk_autorPst AND fk_ParentPst=0 AND fk_Foro=@idforo 
) sub_alias
ORDER BY sub_alias.LastDate desc

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
How to fix display issue, screen flickering issue when I plug in power cord to the machine. Before I start explaining the solution lets check out once the issue how it looks like after I connect the power cord. most of you also have faced this…
Suggested Courses

588 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