CODEWITHSUNDEEP
sqlpostgresqlalias
void
void

Reputation: 3

Getting this SQL Statement right with aliases

what i basically want to do is: compute the difference of to attributes and then get the row with the biggest difference.

So, here is what i tried:

SELECT caller_aor, (end_time - start_time) as duration
    FROM cdrs
    GROUP BY caller_aor
    HAVING duration = (SELECT MAX(end_time - start_time) FROM cdrs);

duration in the HAVING clause gives an error but i cant figure out what i have to do there.

Help is appreciated.

Upvotes: 0

Views: 111

Answers (3)

SingleNegationElimination
SingleNegationElimination

Reputation: 156238

Another way of looking at this is to use a limit.

SELECT caller_aor, (end_time-start_time) AS duration
FROM cdrs
ORDER BY duration DESC
LIMIT 1

Upvotes: 1

Amir Ismail
Amir Ismail

Reputation: 3883

try this query

SELECT top 1 caller_aor, Max(end_time - start_time) as duration
FROM cdrs
GROUP BY caller_aor
Order by duration

Upvotes: 2

Bohemian
Bohemian

Reputation: 425198

You don't want GROUP BY and HAVING (they are only used when aggregating columns).

You want a simple WHERE:

SELECT caller_aor, (end_time - start_time) as duration
FROM cdrs
WHERE (end_time - start_time) = (SELECT MAX(end_time - start_time) FROM cdrs);

Upvotes: 3

Related Questions