SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 RANK functions		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Gigabyte
Starting Member

India
30 Posts
Posted - 06/26/2012 :  14:13:21  Show Profile  Reply with Quote
Is there any way I can create row numbers with out using order by .

I want to generate row number with out any criteria.

My table has only one row.

GIGABYTE+

Lamprey
Flowing Fount of Yak Knowledge

3856 Posts
Posted - 06/26/2012 :  14:20:08  Show Profile  Reply with Quote
Not that I know of. You could ORDER BY NEWID() if you wanted to assign a random numbers. Not sure what you are trying to achomplish though.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 06/26/2012 :  14:24:37  Show Profile  Reply with Quote
For only one row, just do
SELECT 1

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 06/26/2012 :  14:25:16  Show Profile  Reply with Quote
select top 10 row_number() over (order by (select null) )

If there's more than one row
Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

robvolk
Most Valuable Yak

USA
15567 Posts
Posted - 06/26/2012 :  14:27:04  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
Is there any way I can create row numbers with out using order by
No, the ROW_NUMBER() function requires an ORDER BY.
quote:
I want to generate row number with out any criteria
Not sure what you mean by "criteria". If you just want row number then ORDER BY any column, or an expression like Lamprey suggested.
quote:
My table has only one row.
Ummmm, if that's the case what do the earlier statements matter? You're going to get 1 as your row number anyway.
Go to Top of Page

Gigabyte
Starting Member

India
30 Posts
Posted - 06/26/2012 :  14:30:38  Show Profile  Reply with Quote
I am sorry guys ...My table has single column but has 7 crore rows.Column type is varchar. As that column has random data I cannot give row # after sorting. I want to gve row # as they are in table and then do data manipulation.

GIGABYTE+
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5500 Posts
Posted - 06/26/2012 :  14:41:44  Show Profile  Reply with Quote
If you need to track the order that the rows were inserted to the table then you'll need an identity column on the table or a timestamp of some sort. Of course if multiple rows are added in a single statement then the order of those will be randomly assigned. If you have no data in the table that can differentiate the order they were inserted then there is no way to present it in the order it was inserted.

Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000