Archives of the TeradataForum

Message Posted: Thu, 27 Aug 2009 @ 23:03:14 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Just for fun and because I admire the audacity of this method, which I picked up from Tom Kyte's site asktom.oracle.com: concatenate what you want to extract onto the end of what you're sorting, sort it, then substring it out again.

Here's an example that uses sys_calendar.calendar and gets day_of_calendar for the last calendar_date for each group of quarter_of_year, month_of_quarter and week_of_month.

So compared to your original example, quarter_of_year, month_of_quarter and week_of_month are logicalKey1-3, calendar_date is timeStampKey1, and day_of_calendar is desiredAttribute:

     select quarter_of_year, month_of_quarter, week_of_month,
     cast(substr(max(CAST( (CAST (calendar_date as format 'yyyymmdd')) AS
     CHAR(8))  || cast(day_of_calendar as char(10)) ),9) as integer)
     DesiredAttribute
     from sys_calendar.calendar
     where calendar_date between 1090101 and 1091231
     group by 1,2,3

I think this is the ugliest SQL trick I know.

Dave

	<Prev	Next>		<<First	<Prev	Next>	Last>>