BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  How can we select row number in SQL Query ? Goto page 1, 2  Next          BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) View previous topic :: View next topic   Author Message Sunil.P.Thomas Guest Posted: Sat Aug 07, 2004 4:46 am    Post subject: How can we select row number in SQL Query ? Hi , I have a query like this , SELECT Usr_Name_C , Usr_Role_N FROM Adm_User_Mst i want an additional column called SerialNumber like this SELECT SerialNumber , Usr_Name_C , Usr_Role_N FROM Adm_User_Mst but that number should be 1 for first record , 2 for second record and so on. How this query should be written . We are using MS SQL Server 2000 Thanks in advance. Regards Sunil Back to top Arthur Hoornweg Guest Posted: Sat Aug 07, 2004 4:04 pm    Post subject: Re: How can we select row number in SQL Query ? Sunil.P.Thomas wrote: Quote: Hi , I have a query like this , SELECT Usr_Name_C , Usr_Role_N FROM Adm_User_Mst i want an additional column called SerialNumber like this SELECT SerialNumber , Usr_Name_C , Usr_Role_N FROM Adm_User_Mst but that number should be 1 for first record , 2 for second record and so on. How this query should be written . We are using MS SQL Server 2000 Thanks in advance. Regards Sunil Declare an integer identity field in the table. It will auto-increment for every new record. Create table mytable( serialnumber integer identity (1,1) not null, .... ); -- Arthur Hoornweg (please remove the ".net" from my e-mail address) Back to top Martyn Ayers Guest Posted: Sat Aug 07, 2004 6:37 pm    Post subject: Re: How can we select row number in SQL Query ? Arthur, I think what Sunil may have wanted was a sequence number for each row in the result set, which of course an identity column on a base table won't satisfy if the query is a join (or even a single table select which doesn't retrieve all the rows). I don't know how to do that in TransactSql, apart from doing a SELECT ... INTO a temporary table and then addding an ID column afterwards. Sunil: Care to clarify exactly what you're after? Incidentally, if you're using TClientDataSet, it's easy to add an ID column as a calculated column after you're retrieved the result set. Cheers, Martyn Back to top Marc Rohloff [TeamB] Guest Posted: Sat Aug 07, 2004 11:15 pm    Post subject: Re: How can we select row number in SQL Query ? On Sat, 07 Aug 2004 19:37:17 +0100, Martyn Ayers wrote: Quote: Sunil: Care to clarify exactly what you're after? Incidentally, if you're using TClientDataSet, it's easy to add an ID column as a calculated column after you're retrieved the result set. I redirected Sunil here from the c# builder groups so I guess this is ADO.NET. I know in Oracle there is a Pseudo field called ROWNUM but this doesn't help here. -- Marc Rohloff [TeamB] marc rohloff at myrealbox dot com Back to top Arthur Hoornweg Guest Posted: Sun Aug 08, 2004 5:52 pm    Post subject: Re: How can we select row number in SQL Query ? Martyn Ayers wrote: Quote: Arthur, I think what Sunil may have wanted was a sequence number for each row in the result set, which of course ... Martyn, the query doesn't contain an "order by" clause so the sequence of the records returned can be anything. It may be completely random and not reproduceable. So the only way to give the records a reliable number is by storing it in the table itself. -- Arthur Hoornweg (please remove the ".net" from my e-mail address) Back to top Sunil.P.Thomas Guest Posted: Mon Aug 09, 2004 4:06 am    Post subject: Re: How can we select row number in SQL Query ? Martin , Quote: I think what Sunil may have wanted was a sequence number for each row in the result set, which of course an identity column on a base table won't satisfy if the query is a join (or even a single table select which doesn't retrieve all the rows). Yes , this is what i've been trying to ask. What i was trying to do is , i have a query which may result , say 2,000 records. But i don't want this whole data in my local cache , instead i want the data in a small group of say 20 records per fetch. If there is some sequence number we can write the query accordingly like this SELECT 1 AS seq_num , * FROM (SELECT * FROM Adm_User_Mst) as A WHERE seq_num BETWEEN 10 AND 20 But i want a unique sequence number instead of 1 as seq_num. Thanks all for your support. Regards Sunil "Martyn Ayers" <100031.2167atsigncompuserve.com> wrote Quote: Arthur, I think what Sunil may have wanted was a sequence number for each row in the result set, which of course an identity column on a base table won't satisfy if the query is a join (or even a single table select which doesn't retrieve all the rows). I don't know how to do that in TransactSql, apart from doing a SELECT ... INTO a temporary table and then addding an ID column afterwards. Sunil: Care to clarify exactly what you're after? Incidentally, if you're using TClientDataSet, it's easy to add an ID column as a calculated column after you're retrieved the result set. Cheers, Martyn Back to top Arthur Hoornweg Guest Posted: Mon Aug 09, 2004 7:51 am    Post subject: Re: How can we select row number in SQL Query ? Sunil.P.Thomas wrote: Quote: Yes , this is what i've been trying to ask. What i was trying to do is , i have a query which may result , say 2,000 records. But i don't want this whole data in my local cache , instead i want the data in a small group of say 20 records per fetch. If there is some sequence number we can write the query accordingly like this SELECT 1 AS seq_num , * FROM (SELECT * FROM Adm_User_Mst) as A WHERE seq_num BETWEEN 10 AND 20 But i want a unique sequence number instead of 1 as seq_num. You could use a temporary table, created by "select INTO" -- Arthur Hoornweg (please remove the ".net" from my e-mail address) Back to top Sunil.P.Thomas Guest Posted: Mon Aug 09, 2004 8:56 am    Post subject: Re: How can we select row number in SQL Query ? Hi Arthur , how to do that using SELECT INTO Thanks in advance Sunil "Arthur Hoornweg" <arthur.hoornweg (AT) wanadoo (DOT) nl.net> wrote Quote: Sunil.P.Thomas wrote: Yes , this is what i've been trying to ask. What i was trying to do is , i have a query which may result , say 2,000 records. But i don't want this whole data in my local cache , instead i want the data in a small group of say 20 records per fetch. If there is some sequence number we can write the query accordingly like this SELECT 1 AS seq_num , * FROM (SELECT * FROM Adm_User_Mst) as A WHERE seq_num BETWEEN 10 AND 20 But i want a unique sequence number instead of 1 as seq_num. You could use a temporary table, created by "select INTO" -- Arthur Hoornweg (please remove the ".net" from my e-mail address) Back to top Marc Rohloff [TeamB] Guest Posted: Mon Aug 09, 2004 12:28 pm    Post subject: Re: How can we select row number in SQL Query ? On Mon, 9 Aug 2004 09:36:36 +0530, Sunil.P.Thomas wrote: Quote: What i was trying to do is , i have a query which may result , say 2,000 records. But i don't want this whole data in my local cache , instead i want the data in a small group of say 20 records per fetch. If there is some sequence number we can write the query accordingly like this SELECT 1 AS seq_num , * FROM (SELECT * FROM Adm_User_Mst) as A WHERE seq_num BETWEEN 10 AND 20 But i want a unique sequence number instead of 1 as seq_num. I haven't uses MS SQL for many years but IIRC there is a 'TOP' parameter to the select statement which does just this and will do it far more efficiently. -- Marc Rohloff [TeamB] marc rohloff at myrealbox dot com Back to top Jim Elden Guest Posted: Mon Aug 09, 2004 6:36 pm    Post subject: Re: How can we select row number in SQL Query ? Sunil, see this thread http://www.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&threadm=383BF134.8E742EAB%40nojunk.accumedic.com&rnum=2&prev=/groups%3Fas_q%3D%2522SELECT%2520TOP%2522%2520%2522Jim%2520Elden%2522%26safe%3Dimages%26ie%3DUTF-8%26lr%3Dlang_en%26hl%3Den or http://tinyurl.com/682h7 -- Jim Back to top Jim Elden Guest Posted: Mon Aug 09, 2004 6:38 pm    Post subject: Re: How can we select row number in SQL Query ? Hi Marc, See my other post on this :) Best regards, -- Jim Back to top Sunil.P.Thomas Guest Posted: Tue Aug 10, 2004 4:04 am    Post subject: Re: How can we select row number in SQL Query ? Hi Jim , Thank you for your marvellous query. Thanks everyone for your valuable suggestions. Regards Sunil "Jim Elden" <x@yz> wrote Quote: Sunil, see this thread http://www.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&threadm=383BF134.8E74 2EAB%40nojunk.accumedic.com&rnum=2&prev=/groups%3Fas_q%3D%2522SELECT%2520TOP %2522%2520%2522Jim%2520Elden%2522%26safe%3Dimages%26ie%3DUTF-8%26lr%3Dlang_e n%26hl%3Den Quote: or http://tinyurl.com/682h7 -- Jim Back to top Arthur Hoornweg Guest Posted: Tue Aug 10, 2004 10:04 am    Post subject: Re: How can we select row number in SQL Query ? I mean something like Drop table temporarytable; SELECT IDENTITY(int, 1,1) AS sequencenumber, m.name, m.address, m.phonenumber INTO temporarytable FROM mytable m; -- Arthur Hoornweg (please remove the ".net" from my e-mail address) Back to top Sunil.P.Thomas Guest Posted: Tue Aug 10, 2004 10:15 am    Post subject: Re: How can we select row number in SQL Query ? Thanks Arthur , This is one is good . Regards Sunil "Arthur Hoornweg" <arthur.hoornweg (AT) wanadoo (DOT) nl.net> wrote Quote: I mean something like Drop table temporarytable; SELECT IDENTITY(int, 1,1) AS sequencenumber, m.name, m.address, m.phonenumber INTO temporarytable FROM mytable m; -- Arthur Hoornweg (please remove the ".net" from my e-mail address) Back to top corey lawson Guest Posted: Tue Aug 10, 2004 9:17 pm    Post subject: Re: How can we select row number in SQL Query ? "Sunil.P.Thomas" <sunil (AT) netlinkindia (DOT) com> wrote in news:4116f6fa$1 @newsgroups.borland.com: Quote: Martin , I think what Sunil may have wanted was a sequence number for each row in the result set, which of course an identity column on a base table won't satisfy if the query is a join (or even a single table select which doesn't retrieve all the rows). Yes , this is what i've been trying to ask. What i was trying to do is , i have a query which may result , say 2,000 records. But i don't want this whole data in my local cache , instead i want the data in a small group of say 20 records per fetch. If there is some sequence number we can write the query accordingly like this SELECT 1 AS seq_num , * FROM (SELECT * FROM Adm_User_Mst) as A WHERE seq_num BETWEEN 10 AND 20 But i want a unique sequence number instead of 1 as seq_num. Thanks all for your support. Regards Sunil "Martyn Ayers" <100031.2167atsigncompuserve.com> wrote in message news:1v7ah05ttdg5ts8lg0u3p2r45iriddid32 (AT) 4ax (DOT) com... Arthur, I think what Sunil may have wanted was a sequence number for each row in the result set, which of course an identity column on a base table won't satisfy if the query is a join (or even a single table select which doesn't retrieve all the rows). I don't know how to do that in TransactSql, apart from doing a SELECT ... INTO a temporary table and then addding an ID column afterwards. Sunil: Care to clarify exactly what you're after? Incidentally, if you're using TClientDataSet, it's easy to add an ID column as a calculated column after you're retrieved the result set. Cheers, Martyn In general, if you order your dataset first, and then apply the numbering scheme at the outermost layer, it'll be ordered correctly. In oracle, you would do this: select rownum rn, * from ( select b.* from mytable b order by 1,2,3) For temp table, you insert the data as ordered from the result set: insert into temptable select * from real_table where ... order by 1,2,3,.. Then, you would apply the ordering to that... alter table temptable add column rownum identity; (or whatever it is exactly). It also can depend on your access method and database server. In ODBC, you can tell the driver how many records to fetch at once. I think similar in ADO and ADO.Net also. Or, you can use the TOP N statement in SQL Server. etc. There are a variety of ways to kludge around it, but because SQL, in theory, is set-based and not row-based, in theory it's just not inherently possible with pure SQL. Finally, you could code a stored procedure that takes a starting row number and ending row number as input parameters, which internally makes a cursor and uses that to fetch the rows. The last step is probably how Oracle-based drivers do it. They make an anonymous stored proc which sets up a cursor to fetch the number of records desired from a query. Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) All times are GMT Goto page 1, 2  Next Page 1 of 2   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.