• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 912
  • Last Modified:

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

0
techport13
Asked:
techport13
3 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now