Search code examples
ms-accessvbscriptoledbadodb

ADODB SQL Query ORDER BY subquery not working

VBScript code in Classic ASP to pull values including a sub-query, ordering by that sub-query doesn't sort. How can I change this query to sort by the subquery results?

Also tried "ORDER BY 2 ASC;" this doesnt throw an error but does nothing

SELECT tblWP.keyWP, (SELECT SUM(DateDiff('n',tblX.dtmStart,tblX.dtmEnd)) FROM tblX WHERE tblX.keyWP=tblWP.keyWP) AS t1 FROM tblWP ORDER BY t1;

Solution

  • You could try this variation:

    SELECT 
    tblWP.keyWP, 
    Sum(DateDiff('n',tblX.dtmStart,tblX.dtmEnd)) AS t1 
FROM 
    tblWP
INNER JOIN
    tblX
    ON tblWP.keyWP = tblX.keyWPItem
GROUP BY 
    tblWP.keyWP
ORDER BY 
    Sum(DateDiff('n',tblX.dtmStart,tblX.dtmEnd));