Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Retrieving a subset of rows with SQL

 
Paul Bull
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I have an SQL question. I hope this is the appropriate forum for it. I would like to know if there is any way to retrieve a set of rows from a table without using an identifying column ie. primary key. I want to be able to write a JDBC application where I can plug a table name into a query statement and retrieve
a set of rows,ie rows 50 to 100, without knowing any of the column name or values for that table. I tried writing:
SELECT *
FROM tablex
WHERE rownum BETWEEN 50 AND 100;
This doesn't work. I'd appreciate anyones advice.
Thanks,
Paul
 
Daniel Dunleavy
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul,
The select you wrote should work unless your not using Oracle. Obviously there should be more than 50 rows for your statement also. Otherwise it is a valid statement.
It is generally considered bad practice to retrieve rows using the *, since the table can change. If you don't want to know the column names before hand, do some metadata calls to retrieve that information to create the select you need.
HTH
Dan
 
Paul Bull
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dan,
Thanks very much for your reply. Do you happen to know how I could accomplish the task using Oracle? That seems to be our problem. We're trying to retrieve from an Oracle database.
Thanks again,
Paul
 
Paul Bull
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dan,
I think I misread your reply. Did you say it should work if I'm using Oracle? In that case, we did try using the above statement. I tried it in SQLPlus first. It did not seem to work. Do you have any ideas what might be going on?
Thanks,
Paul
 
Daniel Dunleavy
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Check the following...
Does tablex exists...check the systables
are there more than 49 rows?
!! do you have access to the table ? who is the owner of the table and what userid are you using to try and read it. If they are different, the grant must have been issued to access the table.
What error are you getting?
Dan
 
Paul Bull
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Dan,
I have access to the table. It's just a small one we've been using to test with, its got 200 rows. The error I keep getting is "no rows selected". I am certain I'm not going over the total number of rows. I have no problem retrieving data on that table with other SQL statements.
Thanks,
Paul
[This message has been edited by Paul Bull (edited August 09, 2001).]
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul:
From what I have tried in SQL*Plus, I can view rownum for every column but for some reason you can not compare the number in your where clause:
"select rownum, id, name from emp" returns
1, 00001, 'John'
2, 00002, 'Jim'
3, 00003, 'Bob'
but "select rownum, id, name from emp where rownum > 1" returns no rows???
Jamie
 
Daniel Dunleavy
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jamie is right.
If you bad between 1 and 50 it works. But it fails if you don't start at 1. We use sequences on our tables, so we use them all the time which would work.
Dan
 
Paul Bull
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dan,Jamie
Thanks for your help.
Paul
 
Butch Car
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What about something like:
Select id, name from
(select rownum rownumndx, id, name from emp)
where rownumndx>50 and rownumndx<100;
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic