3504: Selected non-aggregate values must be part of the associated group Error on max-case statement

Database
Teradata Employee

Re: 3504: Selected non-aggregate values must be part of the associated group Error on max-case statement

Try using:

select event, logonsource, username, max(logontime) from log_TABLE group by event, logonsource, username

Enthusiast

Re: 3504: Selected non-aggregate values must be part of the associated group Error on max-case statement

Wow it worked :)

But gave me over 90 000 which is way to big answer for 1.5k of users :)

The point is to find last 2 top logins of each user. 

I wonder, maybe I have used wrong sql query for it....

Highlighted
Teradata Employee

Re: 3504: Selected non-aggregate values must be part of the associated group Error on max-case statement

Perhaps this code would do what you need:

SELECT event, logonsource, username, RANK() OVER (PARTITION BY username ORDER BY logontime DESC) AS RankedLogons FROM log_TABLE QUALIFY  RankedLogons <=2;

Enthusiast

Re: 3504: Selected non-aggregate values must be part of the associated group Error on max-case statement

Lol... how I didnt find it myself? :)

Thanks Wasif! You are great help!

Enthusiast

Re: 3504: Selected non-aggregate values must be part of the associated group Error on max-case statement

Hi there again.

I mus say that I could not find any proper syntax explanation in teradata document about RANK() OVER. How to make the select so it will show it will return top date result for each login?

I must say works or not method does not work :)

Teradata Employee

Re: 3504: Selected non-aggregate values must be part of the associated group Error on max-case statement

A complete reference to the Teradata SQL: Functions & Operators is provided in the below document:

http://www.info.teradata.com/do_redirect.cfm?itemid=072600012