Subquery select select only one col

Question

I have this query who create pagination system, I want to SELECT only A* , I dont want to show row_number value even if I need it .

SELECT *
  FROM (SELECT A.*, rownum row_number
          FROM (select * from dual
               ) A
         WHERE rownum <= 10) 
 WHERE row_number >= 1

The result :

D ROW_NUMBER
- ----------
X          1

Whats I want

D 
- 
X

Thanks for help

You'll have to *list the column names* explicitely. there is **no way** in `SQL` to show *all column with some exception columns*. I.e. `select DUMMY from ....` — Marmite Bomber, Dec 19 '19 at 11:06
@MarmiteBomber is right. You either select * or you specify the fields. https://stackoverflow.com/a/9133172/8943967. Or you can start doing some PL/SQL stuff to achieve what you want. — Ergi Nushi, Dec 19 '19 at 11:11
@ErgiNushi Hello ,Is a dynamic query I can't select specific col — DEVLOGIN, Dec 20 '19 at 09:23
@DEVLOGIN. Did you check this? https://stackoverflow.com/a/33220953/4295763 — Arun Palanisamy, Dec 20 '19 at 10:31

score 0 · Answer 1 · answered Dec 19 '19 at 17:34

If your table has a primary key, you may perform the pagination filter only on this key and in the second step select the data based on the PK.

This will allow you to use SELECT *

select * from tab 
where id in (
SELECT id
  FROM (SELECT id, rownum row_number
          FROM (select id from tab
               ) A
         WHERE rownum <= 10) 
 WHERE row_number >= 1)

You'll pay a little performance penalty, as each selected row must be additionaly accessed by the primary key index (but this will be not visible for 10 rows or so).

An other point with pagination is that you typically need to present the data in some order and not randonly as in your example.

In that case the innermost subquery will be

select id from tab order by <some column>

Here you can profit, as you need to sort only the PK and the sort key and not the whole row (but again it will be not visible for 10 rows).

Subquery select select only one col

1 Answers1