[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 890
  • 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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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