ORDER BY Subquery result under access SQL

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

LVL 2
DrakecoldwinterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
DrakecoldwinterAuthor Commented:
it gives me a syntax error in ORDEN BY clause
0
Jinesh KamdarCommented:
@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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

DrakecoldwinterAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DrakecoldwinterAuthor Commented:
thank a lot
0
DrakecoldwinterAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.