CODEWITHSUNDEEP
sqlms-access
user706965
user706965

Reputation: 13

MS Access Query - Ignoring 'Order By' Clause

SELECT u.UserLastName, u.UserID, SUM((Format(c.CallLength, 'h') * 60 *60)) as hourSeconds, SUM((Format(c.CallLength, 'n') * 60)) as minSeconds,
                SUM((Format(c.CallLength, 's'))) as seconds, COUNT(*) as 'callCount'
                FROM Calls AS c INNER JOIN User AS u ON c.UserID = u.UserID
                WHERE c.CallDate BETWEEN format(NOW(), 'yyyy-mm-dd 00:00:00') AND format(Now(), 'yyyy-mm-dd 23:59:59') AND u.UserLastName NOT IN ('Britt','Jason','System')
                GROUP BY u.UserID, u.UserLastName
                ORDER BY 'callCount' DESC;

I've spent forever trying different techniques to sort this query using the "ORDER BY" clause. What is incorrect? It simply runs the query with no errors but seems to sort by the u.UserID field instead. No matter what I do I cannot get the ORDER BY clause to order any field!

Upvotes: 1

Views: 3649

Answers (2)

HansUp
HansUp

Reputation: 97101

If your original query returned the data you want without error, and the only problem was the ORDER BY, I think this simple change is the way to go.

SELECT
    u.UserLastName,
    u.UserID,
    SUM((Format(c.CallLength, 'h') * 60 *60)) as hourSeconds,
    SUM((Format(c.CallLength, 'n') * 60)) as minSeconds,
    SUM((Format(c.CallLength, 's'))) as seconds,
    COUNT(*) as callCount
FROM Calls AS c INNER JOIN User AS u ON c.UserID = u.UserID
WHERE
    c.CallDate BETWEEN format(NOW(), 'yyyy-mm-dd 00:00:00')
    AND format(Now(), 'yyyy-mm-dd 23:59:59')
    AND u.UserLastName NOT IN ('Britt','Jason','System')
GROUP BY u.UserID, u.UserLastName
ORDER BY 6 DESC;

When you assign an alias to a field (or expression), you can't use that alias name in the ORDER BY. However you can refer to it by its ordinal position in the field list.

Upvotes: 2

The Lazy Coder
The Lazy Coder

Reputation: 11828

you dont want to use a string as a column name.

Try this.

also, if I recall correctly, you cant order and group. so a sub select groups, and you can order the results...

Select * from (
    SELECT 
        u.UserLastName, 
        u.UserID, 
        SUM((Format(c.CallLength, 'h') * 60 *60)) as hourSeconds, 
        SUM((Format(c.CallLength, 'n') * 60)) as minSeconds,
        SUM(Format(c.CallLength, 's')) as seconds, 
        COUNT(*) as callCount 
    FROM Calls AS c 
    INNER JOIN User AS u ON c.UserID = u.UserID

    WHERE c.CallDate BETWEEN format(NOW(), 'yyyy-mm-dd 00:00:00') AND 
          format(Now(), 'yyyy-mm-dd 23:59:59') AND 
          u.UserLastName NOT IN ('Britt','Jason','System')
    GROUP BY u.UserID, u.UserLastName
)
ORDER BY callCount DESC;

if Your column name cannot be used because it is a keyword or multiple words. try putting square braces around it.

[callCount]

Upvotes: 1

Related Questions