temporary numbering of select statement

justinr

I am looking for a way to create a temporary column which will keep a running count of a select statement. For example,

select * from table where field like '%engineering%'

let's say this returns 30 rows, what I would like to do is have a column say tempCount to actually contain the values of the rows i.e.

tempCount field someCol
1 eng... someData
2 ... ...
3

and so on.

This may not be feasible, or the wrong way of going about this so any information would be greatly appreciated. Thanks

Sxooter

This is one of those things that's much easier to do in PHP than in SQL, generally speaking, and if you can get your database to do it, no two will do it the same, so, it won't be portable.

I just do it like this:

$query  "select * from table";
$res = pg_query($conn,$query);
$count = pg_num_rows($res);
for ($i=0;$i<$count;$i++){
    print "row: ".$i." ". (rest of data goes here);
}

justinr

Yeah that would work if I wanted to print that data out, but that's not what I wanted to do. Sorry I didn't explain what I wanted to do in my last post. What I want to do is select a certain number of rows, for instance google search engine limits the number of pages shown on one page to what you specify. I want to do something similar, but that is where the problem is. The database software we are using is Sybase ASA 6.0 (pretty old) and there is no limit command like in MySQL. I did however find a command that will work if I am able to number the fields... It goes like so:

select * from table where tempCount BETWEEN 1 and 10;

this will allow me to preform the operation without the limit rows command used in MySQL. I hope this might help a little more. Thanks for response. I should have been more descriptive.

Justin

Sxooter

Ahhh that helps.

So, does Sybase support cursors? If so, look up declare cursor and see if you can use that.

justinr

Yes it supports cursors. I will look into it and see if this will be able to do what I would like to do. Thank you very much for your help.

Justin