DB2 Z/OS Select 10 rows of each different type from a single table

I am running DB2 Z/OS (mainframe) v (i think it's) 8

I have a table that contains two fields, id and type, sample data below.

I need to return 10 records, sorted descending by id, for each of the different types in the table.

I would like to do this with one query.
id  type
1   1
2   1
3   1
4   1
5   2
6   2
7   2
8   3
9   3
10  4
11  5
etc etc

Open in new window

techport13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi techport,

If you've got the OLAP extensions, this is a piece of cake.  :)  If not, it'll take a bit more work.


Good Luck,
Kent

SELECT id, type
FROM
(
  SELECT id, type, row_number () over (partition by id, type) as rn
  FROM mytable
) t0
WHERE rn <= 10
order by 1, 2;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
techport13Author Commented:
From DB2 Command Editor:

------------------------------ Commands Entered ------------------------------
SELECT logical_name, type
FROM
(
  SELECT logical_name, type, row_number () over (partition by logical_name, type) as rn
  FROM pg#17.device
) t0
WHERE rn <= 10
order by 1, 2;
------------------------------------------------------------------------------
SELECT logical_name, type FROM ( SELECT logical_name, type, row_number () over (partition by logical_name, type) as rn FROM pg#17.device ) t0 WHERE rn <= 10 order by 1, 2
SQL0104N  An unexpected token "(" was found following "".  Expected tokens may
include:  ", FROM INTO".  SQLSTATE=42601

SQL0104N  An unexpected token "(" was found following "".  Expected tokens may include:  ", FROM INTO                                                        ".

Explanation:

A syntax error in the SQL statement or the input command string
for the SYSPROC.ADMIN_CMD procedure was detected at the specified
token following the text "<text>".  The "<text>" field indicates
the 20 characters of the SQL statement or the input command
string for the SYSPROC.ADMIN_CMD procedure that preceded the
token that is not valid.  

 As an aid, a partial list of valid tokens is provided in the
SQLERRM field of the SQLCA as "<token-list>".  This list assumes
the statement is correct to that point.  

 The statement cannot be processed.  

User Response:

Examine and correct the statement in the area of the specified
token.  

 sqlcode :  -104

 sqlstate :  42601

0
SharathData EngineerCommented:
check this:
SELECT TOP 10 type,id FROM(
SELECT type,MAX(id) AS id FROM yourtable GROUP BY type) A
 ORDER BY id DESC, type ASC
0
momi_sabagCommented:
version 8 on zos does not support the olap extension functions, so you will have to take a different approach in order to implement your solution
can you explain why you need such a result set?
0
techport13Author Commented:
I decided to just write out the queries myself, since there were only 15 or so unique types.

select * from table where type=1 order by id desc fetch first 10 rows only
etc etc.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.