Discussion:
Accessing Top N rows from SQL MP/MX table
(too old to reply)
Prashant
2021-05-04 16:44:10 UTC
Permalink
Hello,

Can someone help me how I can get top n (say 10 or 20) rows from SQL table using SELECT query without using SET LIST_COUNT?
Randall
2021-05-04 16:54:47 UTC
Permalink
Post by Prashant
Hello,
Can someone help me how I can get top n (say 10 or 20) rows from SQL table using SELECT query without using SET LIST_COUNT?
SELECT [FIRST 10] * FROM ...
Bill Honaker
2021-05-04 19:19:06 UTC
Permalink
Post by Randall
Post by Prashant
Hello,
Can someone help me how I can get top n (say 10 or 20) rows from SQL table using SELECT query without using SET LIST_COUNT?
SELECT [FIRST 10] * FROM ...
I believe that works for SQL/MX, but not for SQL/MP. To use it against MP tables you'll need to use MPALIAS and the MX executor.
Randall
2021-05-04 19:24:37 UTC
Permalink
Post by Randall
Post by Prashant
Hello,
Can someone help me how I can get top n (say 10 or 20) rows from SQL table using SELECT query without using SET LIST_COUNT?
SELECT [FIRST 10] * FROM ...
I believe that works for SQL/MX, but not for SQL/MP. To use it against MP tables you'll need to use MPALIAS and the MX executor.
That's a point. SQL/MP and SQL/MX have very different query languages. Using MPALIAS would be correct since MX has the capability. We don't know what the OP intended, though, so all advice is purely guesswork.
JShepherd
2021-05-04 20:51:21 UTC
Permalink
In article <***@4ax.com>, ***@x_i_d.com
says...
Post by Prashant
Post by Randall
Post by Prashant
Hello,
Can someone help me how I can get top n (say 10 or 20) rows from SQL table us
ing SELECT query without using SET LIST_COUNT?
Post by Randall
SELECT [FIRST 10] * FROM ...
I believe that works for SQL/MX, but not for SQL/MP. To use it against MP table
s you'll need to use MPALIAS and the MX executor.
mxci can directly access SQL/MP tables


Hewlett Packard Enterprise NonStop(TM) SQL/MX Conversational Interface 3.3
(c) Copyright 2003-2015 Hewlett Packard Enterprise Development LP.
Post by Prashant
Post by Randall
select [first 10] * from $data01.sql.table;
PK_TEXT PK_TS COMMAND C1
-------- ---------- ---------------- -----------------
0 0 pathcom status pathway
Bill Honaker
2021-05-04 21:17:02 UTC
Permalink
Post by JShepherd
says...
Post by Prashant
Post by Randall
Post by Prashant
Hello,
Can someone help me how I can get top n (say 10 or 20) rows from SQL table us
ing SELECT query without using SET LIST_COUNT?
Post by Randall
SELECT [FIRST 10] * FROM ...
I believe that works for SQL/MX, but not for SQL/MP. To use it against MP table
s you'll need to use MPALIAS and the MX executor.
mxci can directly access SQL/MP tables
Hewlett Packard Enterprise NonStop(TM) SQL/MX Conversational Interface 3.3
(c) Copyright 2003-2015 Hewlett Packard Enterprise Development LP.
Post by Prashant
Post by Randall
select [first 10] * from $data01.sql.table;
PK_TEXT PK_TS COMMAND C1
-------- ---------- ---------------- -----------------
0 0 pathcom status pathway
You're correct. I'd forgotten, as our standards (and all customer's standards) have always requierd either an ANSI name or DEFINE name.

MPALIAS is not required.
Prashant
2021-05-05 16:38:14 UTC
Permalink
Post by JShepherd
says...
Post by Prashant
Post by Randall
Post by Prashant
Hello,
Can someone help me how I can get top n (say 10 or 20) rows from SQL table us
ing SELECT query without using SET LIST_COUNT?
Post by Randall
SELECT [FIRST 10] * FROM ...
I believe that works for SQL/MX, but not for SQL/MP. To use it against MP table
s you'll need to use MPALIAS and the MX executor.
mxci can directly access SQL/MP tables
Hewlett Packard Enterprise NonStop(TM) SQL/MX Conversational Interface 3.3
(c) Copyright 2003-2015 Hewlett Packard Enterprise Development LP.
Post by Prashant
Post by Randall
select [first 10] * from $data01.sql.table;
PK_TEXT PK_TS COMMAND C1
-------- ---------- ---------------- -----------------
0 0 pathcom status pathway
You're correct. I'd forgotten, as our standards (and all customer's standards) have always requierd either an ANSI name or DEFINE name.
MPALIAS is not required.
Hello All,
Thanks a lot for valuable help. SELECT [FIRST 10] helped me to solve what I am looking for.
I have SQLMP table and I created it's Alias in SQLMX.

I do have one more query relates to same, I can see in SQLMX manual, we can use ROWNUM in SELECT query for 3.6 version and above but it doesn't support SQLMP table or Alias created in SQLMX. Is there any way
I can get ROWNUM from SQLMP table or Alias? Please suggest.
Randall
2021-05-05 17:31:50 UTC
Permalink
Post by Prashant
Post by JShepherd
says...
Post by Prashant
Post by Randall
Post by Prashant
Hello,
Can someone help me how I can get top n (say 10 or 20) rows from SQL table us
ing SELECT query without using SET LIST_COUNT?
Post by Randall
SELECT [FIRST 10] * FROM ...
I believe that works for SQL/MX, but not for SQL/MP. To use it against MP table
s you'll need to use MPALIAS and the MX executor.
mxci can directly access SQL/MP tables
Hewlett Packard Enterprise NonStop(TM) SQL/MX Conversational Interface 3.3
(c) Copyright 2003-2015 Hewlett Packard Enterprise Development LP.
Post by Prashant
Post by Randall
select [first 10] * from $data01.sql.table;
PK_TEXT PK_TS COMMAND C1
-------- ---------- ---------------- -----------------
0 0 pathcom status pathway
You're correct. I'd forgotten, as our standards (and all customer's standards) have always requierd either an ANSI name or DEFINE name.
MPALIAS is not required.
Hello All,
Thanks a lot for valuable help. SELECT [FIRST 10] helped me to solve what I am looking for.
I have SQLMP table and I created it's Alias in SQLMX.
I do have one more query relates to same, I can see in SQLMX manual, we can use ROWNUM in SELECT query for 3.6 version and above but it doesn't support SQLMP table or Alias created in SQLMX. Is there any way
I can get ROWNUM from SQLMP table or Alias? Please suggest.
I don't think you will get a happy answer to this one. SQL/MP is based on top of ENSCRIBE files. The notion of a record number does not actually exist across ENSCRIBE file types so it is unlikely that there is a general solution. Of course, there are inefficient ones, where you could build populate an index of primary keys by record number. The difficulty is that you could create an unkeyed table based on a RELATIVE file, delete a row in the middle of the table, and your row numbers would be off because of a gap. In addition, a FUP RELOAD could invalidate any location information, including relative byte offsets (RBAs) that are used to locate records (the hard way) in ENSCRIBE. I might be wrong - my SQL/MP is a bit rusty.
wbreidbach
2021-05-17 09:09:00 UTC
Permalink
Post by Randall
Post by Prashant
Post by JShepherd
says...
Post by Prashant
Post by Randall
Post by Prashant
Hello,
Can someone help me how I can get top n (say 10 or 20) rows from SQL table us
ing SELECT query without using SET LIST_COUNT?
Post by Randall
SELECT [FIRST 10] * FROM ...
I believe that works for SQL/MX, but not for SQL/MP. To use it against MP table
s you'll need to use MPALIAS and the MX executor.
mxci can directly access SQL/MP tables
Hewlett Packard Enterprise NonStop(TM) SQL/MX Conversational Interface 3.3
(c) Copyright 2003-2015 Hewlett Packard Enterprise Development LP.
Post by Prashant
Post by Randall
select [first 10] * from $data01.sql.table;
PK_TEXT PK_TS COMMAND C1
-------- ---------- ---------------- -----------------
0 0 pathcom status pathway
You're correct. I'd forgotten, as our standards (and all customer's standards) have always requierd either an ANSI name or DEFINE name.
MPALIAS is not required.
Hello All,
Thanks a lot for valuable help. SELECT [FIRST 10] helped me to solve what I am looking for.
I have SQLMP table and I created it's Alias in SQLMX.
I do have one more query relates to same, I can see in SQLMX manual, we can use ROWNUM in SELECT query for 3.6 version and above but it doesn't support SQLMP table or Alias created in SQLMX. Is there any way
I can get ROWNUM from SQLMP table or Alias? Please suggest.
I don't think you will get a happy answer to this one. SQL/MP is based on top of ENSCRIBE files. The notion of a record number does not actually exist across ENSCRIBE file types so it is unlikely that there is a general solution. Of course, there are inefficient ones, where you could build populate an index of primary keys by record number. The difficulty is that you could create an unkeyed table based on a RELATIVE file, delete a row in the middle of the table, and your row numbers would be off because of a gap. In addition, a FUP RELOAD could invalidate any location information, including relative byte offsets (RBAs) that are used to locate records (the hard way) in ENSCRIBE. I might be wrong - my SQL/MP is a bit rusty.
If you want to see the first N rows you can use the Report Writer even with SQL/MP. There is a manual for that.
You can do it like this:
SET LIST_COUNT 0;
Select.....
LIST 10;

JShepherd
2021-05-04 17:15:13 UTC
Permalink
Post by Prashant
Hello,
Can someone help me how I can get top n (say 10 or 20) rows from SQL table
using
Post by Prashant
SELECT query without using SET LIST_COUNT?
What do you mean by 'TOP' ?

The first 20 rows, like the top of a report
or 20 rows with the highest key value ?
Loading...