Sql statement to find records from x to y

Question

Sql statement to find records from x to y

serkan_sendur

SSC Veteran

Points: 241
More actions
January 29, 2009 at 10:28 am

#155199

Hi guys,
I want to select records from the database, i need to pass start index and end index, then the result set will include those records.
Let say my database table has 100 records, i want to get the records between 30-50. How can i do that?

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Really ambiguous question, so hard to give a good answer. May I suggest that read the first article I have linked below in my signature block. Follow the guidelines in that article and I'm sure you will get a much better answer.

serkan_sendur SSC Veteran Points: 241 More actions · Answer 2

the question is not ambigious in fact. let say i have an array of 100 elements, i want to get two inputs from the user, one the start index, the second is the end index. i can do something like this right:

int[] myArray = {....};

int startIndex = 20;

int endIndex = 80;

for(int i=startIndex; i=<endIndex;i++)

{

Console.Writeln(myArray);

}

i want something like that from my database. It shouldnt be hard..

serkan_sendur SSC Veteran Points: 241 More actions · Answer 3

i found it at this address:

http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

serkan_sendur (1/29/2009)
the question is not ambigious in fact. let say i have an array of 100 elements, i want to get two inputs from the user, one the start index, the second is the end index. i can do something like this right:
int[] myArray = {....};
int startIndex = 20;
int endIndex = 80;
for(int i=startIndex; i=<endIndex;i++)
{
Console.Writeln(myArray);
}
i want something like that from my database. It shouldnt be hard..

Not the same. In any RDBMS there is no implied order of the data stored. The only place this is not true is when you start looking at a clustered index on a table.

In your array example, once the data is inserted, it will always be in that order. Based on the above, this is not true with a table in a database.

GSquared SSC Guru Points: 260824 More actions · Answer 5

You can use Row_Number in a CTE to number the rows, then select based on that numbering. Is that what you're looking for?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

You know, GSquared, that may be what he was looking for, but I didn't want to point him that way if that wasn't what he was looking for. I mean, even that can change depending on how you order it in the OVER clause.

GSquared SSC Guru Points: 260824 More actions · Answer 7

It's my best guess of what he's looking for. That's why I end posts like that with, "Does that help?", or something to that effect.

And, yes, it's very sensitive to the Over clause. I'm assuming/hoping he can figure that part out if he looks up Row_Number. Since Row_Number isn't exactly the most famous function in SQL Server, and is new to 2005 (and thus largely unknown to SQL 2000 devs), sometimes pointing people at it is enough.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

serkan_sendur SSC Veteran Points: 241 More actions · Answer 8

thanks for your replies guys, i was looking for row number method, i found it. Actually i am not using sql server, i am using raima database, i just needed an analogy for what i needed to achieve. i dont know how to set the thread as solved, but it is solved for me.

Thanks again.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Heard of raima database, not sure, however, how SQL Server relates.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Hmm, interesting. Embedded, always on database system. Curious as to its capabilities.

serkan_sendur SSC Veteran Points: 241 More actions · Answer 11

as a c# guy, it bothers me to work with that database using c and c++, our version doesnot even support sql statements. raima allocates memory for the data and fills it later we grap that data using pointer stuff. it is just my bad fate..

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Found this, I did, on their web site:

SQLRowld

Retrieve identifier of current table row

Prototype

SQLRETURN SQLRowId

(SQLHSTMT hstmt, SQLCHAR *tblname, SQLUINTEGER *pRowid)Parameters

hstmt

The handle for the SQL statement that references the table.

tblname

The table name. If correlation names were used for the tables listed in the from clause of the referencing select statement, you must specify the correlation name.

pRowid

A pointer to the row identifier.

Description

This function retrieves the identifier of the current row from the given table being accessed by the specified SQL statement. If the statement is insert, it must have been executed prior to your application's call to SQLRowId If the statement is select, update, or delete, it must have been positioned before your application calls SQLRowId. If the statement is select, it must also not contain an order by or a group by clause. The function returns SQL_ERROR if the specified table is not referenced in the SQL statement.

This function is useful when using primary keys of the rowid type. For example, after your application executes an insert statement to insert a row in a table with a primary key of the rowid type, a call to SQLRowId will retrieve the identifier of the inserted row. Your application can then specify this row identifier in any insert statements using foreign keys that reference the table containing the row.

Return Codes

SQL_ERROR

SQL_SUCCESS

SQL_INVALID_HANDLE