Limit? Rowid?

Hi,
I am writing a "universal" db-Interface and just finished the postgres
and mysql part. I got somehow stuck on the MS SQL Server part (via
ODBC).
I have no access to the SQL Server CD or the machine where it is
running, I access the database only via a VPN tunnel and ODBC.
My question is:
How can I use something like the "LIMIT " command?
How can I get the actual "_rowid" (not the primary key, but the row-id
of the result set.)
Something like:
Select * from article Limit 5,10
Select _rowid from article where id = "XYZ-123"
Update article set amount = 15 where _rowid = 23
And how to get the table definition, something like "Describe article;"
oder "Show create table article;" ?
Is there a simple online reference for this SQL dialect that SQL Server
via ODBC uses?
Thank you for your help
RobertRobert
What vesrion are you using?
SQL Server 2005
There is new function ROW_NUMBER()
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
columns
FROM Table
) AS Der WHERE rownum BETWEEN 5 AND 10
SQL Server 2000
SELECT * FROM Table WHERE (SELECT COUNT(*) FROM Table t WHERE
t.PK<=Table.PK)
BETWEEN 5 AND 10
pk-Primary keys
Actualy if it does not help you ,please post DDL+ sample data + an expected
result
"Robert Eisig" <quantumchaos666@.gmx.net> wrote in message
news:1143380427.955422.222930@.u72g2000cwu.googlegroups.com...
> Hi,
> I am writing a "universal" db-Interface and just finished the postgres
> and mysql part. I got somehow stuck on the MS SQL Server part (via
> ODBC).
> I have no access to the SQL Server CD or the machine where it is
> running, I access the database only via a VPN tunnel and ODBC.
> My question is:
> How can I use something like the "LIMIT " command?
> How can I get the actual "_rowid" (not the primary key, but the row-id
> of the result set.)
> Something like:
> Select * from article Limit 5,10
> Select _rowid from article where id = "XYZ-123"
> Update article set amount = 15 where _rowid = 23
> And how to get the table definition, something like "Describe article;"
> oder "Show create table article;" ?
> Is there a simple online reference for this SQL dialect that SQL Server
> via ODBC uses?
> Thank you for your help
> Robert
>|||Robert Eisig wrote:
> Hi,
> I am writing a "universal" db-Interface and just finished the postgres
> and mysql part. I got somehow stuck on the MS SQL Server part (via
> ODBC).
> I have no access to the SQL Server CD or the machine where it is
> running, I access the database only via a VPN tunnel and ODBC.
> My question is:
> How can I use something like the "LIMIT " command?
> How can I get the actual "_rowid" (not the primary key, but the row-id
> of the result set.)
> Something like:
> Select * from article Limit 5,10
> Select _rowid from article where id = "XYZ-123"
> Update article set amount = 15 where _rowid = 23
> And how to get the table definition, something like "Describe article;"
> oder "Show create table article;" ?
> Is there a simple online reference for this SQL dialect that SQL Server
> via ODBC uses?
> Thank you for your help
> Robert
There is no such "rowid" concept in SQL Server. An exposed row number
based on anything other than values in the table violates the
relational Information Principle. So it's not a feature we should
expect or desire from a well-designed SQL DBMS - something you should
bear in mind if you want to create an app that is portable across many
database products.
SQL Server 2005 does support the ANSI standard RANK() and ROW_NUMBER()
functions, which derive their values from the column(s) specified with
the ORDER BY syntax. Make sure you specify a unique key for ORDER BY so
as to guarantee a deterministic result.
See my signature for a link to the SQL Server docs.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Limit is what we use in MYSQL no?
We can consider TOP clause of Sql Server similar to LIMIT.
Like,
Select * from article Limit 5
select top 5 * from article
For a specific range...
Select * from article Limit 5,10
Playing with 'IN' clause you can get LIMIT 5-10.
select TOP 5 * from article where articleid in (Select TOP 10 articleid
from article ) ORDER BY DESC
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Robert Eisig" wrote:
> Hi,
> I am writing a "universal" db-Interface and just finished the postgres
> and mysql part. I got somehow stuck on the MS SQL Server part (via
> ODBC).
> I have no access to the SQL Server CD or the machine where it is
> running, I access the database only via a VPN tunnel and ODBC.
> My question is:
> How can I use something like the "LIMIT " command?
> How can I get the actual "_rowid" (not the primary key, but the row-id
> of the result set.)
> Something like:
> Select * from article Limit 5,10
> Select _rowid from article where id = "XYZ-123"
> Update article set amount = 15 where _rowid = 23
> And how to get the table definition, something like "Describe article;"
> oder "Show create table article;" ?
> Is there a simple online reference for this SQL dialect that SQL Server
> via ODBC uses?
> Thank you for your help
> Robert
>|||The concept of rowid or recid on other database products and row_number() in
SQL Server have nothing in common. rowid is permanent on a record event after
database shutdown. row_number() in SQL Server is just a sequential number
within a partition of a result set.
Ben Nevarez, MCDBA, OCP
Database Administrator
"David Portas" wrote:
> Robert Eisig wrote:
> > Hi,
> >
> > I am writing a "universal" db-Interface and just finished the postgres
> > and mysql part. I got somehow stuck on the MS SQL Server part (via
> > ODBC).
> >
> > I have no access to the SQL Server CD or the machine where it is
> > running, I access the database only via a VPN tunnel and ODBC.
> >
> > My question is:
> >
> > How can I use something like the "LIMIT " command?
> >
> > How can I get the actual "_rowid" (not the primary key, but the row-id
> > of the result set.)
> >
> > Something like:
> >
> > Select * from article Limit 5,10
> >
> > Select _rowid from article where id = "XYZ-123"
> >
> > Update article set amount = 15 where _rowid = 23
> >
> > And how to get the table definition, something like "Describe article;"
> > oder "Show create table article;" ?
> >
> > Is there a simple online reference for this SQL dialect that SQL Server
> > via ODBC uses?
> >
> > Thank you for your help
> > Robert
> There is no such "rowid" concept in SQL Server. An exposed row number
> based on anything other than values in the table violates the
> relational Information Principle. So it's not a feature we should
> expect or desire from a well-designed SQL DBMS - something you should
> bear in mind if you want to create an app that is portable across many
> database products.
> SQL Server 2005 does support the ANSI standard RANK() and ROW_NUMBER()
> functions, which derive their values from the column(s) specified with
> the ORDER BY syntax. Make sure you specify a unique key for ORDER BY so
> as to guarantee a deterministic result.
> See my signature for a link to the SQL Server docs.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Ben Nevarez wrote:
> The concept of rowid or recid on other database products and row_number() in
> SQL Server have nothing in common. rowid is permanent on a record event after
> database shutdown. row_number() in SQL Server is just a sequential number
> within a partition of a result set.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
That's correct. A permanent "rowid" violates the Information Principle.
A ROW_NUMBER() function (provided it is generated deterministically
from data in the table) does not.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--