Selecting sets of records within table

Database
Visitor

Selecting sets of records within table

Hello. I acknowledge and apologize in advance that I am brand new to Teradata, and am very green in pulling records from extremely large databases.

 

What I need to do is pull groups of records from within a table. The table has tens of thousands of records showing discussion/log entries onto customer accounts. The entries are designated by a code used to summarize what the discussion was about. I need to pull the most recent 5 entries for each of about 500 codes in the table. In 3 days of trying to help myself (with help of various forum topics), for the life of me I cannot come up with the code to pull these groups of 5 entries on each of the codes. i have tried about 30 different methods/versions.

 

This is where I am at this point. And it just won't pull the groupings. (I came to use the Partition method after reading another topic in the forums.) In the query below I had given up on pulling the most recent (max date) entries, instead just trying get my groups of 5.  I've tried moving my SAMPLE location, etc. Thank you in advance for any assistance. You're welcome to laugh....

 

SELECT

TYPE_CD

,CLIENT

,USERID

,DATE

FROM

(SELECT

TYPE_CD

,CLIENT_NO

,USERID

,DATE

,ROW_NUMBER() OVER (PARTITION BY TYPE_CD,CLIENT,USERID ORDER BY DATE) AS TMP_KMT

FROM DATA_DATABASE.SECTION_TABLE

) DT

SAMPLE 5

WHERE TYPE_CD in ('082','088','148') AND CLIENT IN ('708')

ORDER by 1,2,3

GROUP BY

TYPE_CD

,CLIENT

,USERID

,DATE

1 REPLY
Highlighted
Junior Contributor

Re: Selecting sets of records within table

Your description translates to:

SELECT *
FROM DATA_DATABASE.SECTION_TABLE
QUALIFY
   ROW_NUMBER()
   OVER (PARTITION BY TYPE_CD      -- for each code
         ORDER BY DATE DESC) <= 5  -- most recent 5 entries