SQL Tips for DB2 LUW

with Tags: by

Featured Blog Entries

All posts

Who's the most popular kid on the block?

Comments (8) Visits (11618)

Background Roberto from Italy sent me the following question: Is there something like the STATS_MODE() function in DB2? He says STATS_MODE() is supposed to return the most frequently occurring value from a multi-set of values. A bit of rifling through the internet, steering clear of zones forbidden to me, reveals that STATS_MODE is an aggregate function similar to SUM() or AVG(). The function returns the most frequent value within a group. If there are two equally frequent values one of them will be picked with no specific rule. DB2 does not... [More]

Tags: by frequent group olap values stats_mode

The trouble of combining DISTINCT with ORDER BY

Comments (4) Visits (69873)

Motivation Yesterday one of my coworkers from India asked for my help in migrating the following SQL statement from Sybase to DB2. SELECT DISTINCT c1, c2 FROM t ORDER BY c3 When this statement is run in DB2 (assuming the appropriate definition of "T") the following error is being returned: SQL0214N An expression in the ORDER BY clause in the following position, or starting with "C3" in the "ORDER BY" clause is not valid. Reason code = "2". Why does DB2 raise this error? The reason is simple: It's not... [More]

Tags: olap by order distinct sybase row_number over

Blogs

SQL Tips for DB2 LUW

About this blog

Links

Tags

Recent tweets

Who's the most popular kid on the block?

The trouble of combining DISTINCT with ORDER BY