1

In my project,i am allowing user to select database of his choice from given options & i need to write queries considering all these optional databases. So as per my one of requirement,is there any way to achieve generic functionality similar to ROW_NUMBER() in MSSQL ?

FullStack
  • 665
  • 11
  • 26
  • 7
    `ROW_NUMBER()` *is* the generic common way. It is ANSI standard and supported by basically all databases. Oh, not MySQL, but what you can do about databases that don't support standard functionality? – Gordon Linoff Jan 14 '15 at 11:47
  • I was tempted to call you troll. Then I realized you were just telling the truth :/ For completeness: [the feature can be emulated](http://stackoverflow.com/q/4571317/1446005) fairly easily. – RandomSeed Jan 14 '15 at 11:54
  • ROW_NUMBER is included in the ANSI/ISO SQL standard as Feature T612, Advanced OLAP operations. (I.e. not a core-SQL functionality.) – jarlh Jan 14 '15 at 11:57
  • 3
    @RandomSeed: the simple case _can_ indeed be simulated easily. But when you have things like `partition by col1, col2, col3 order by col4, col5 desc` things get **really** messy when using query variables (and I'm not even talking about using two different windows in the same query) –  Jan 14 '15 at 11:57
  • Fair enough, not everything can be worked around. In my poor attempt to restore some balance (arguments against MySQL are often biaised), I should have just mentionnned that no RDBMS (that I know of) implements the full ANSI SQL specs. – RandomSeed Jan 14 '15 at 12:02
  • ... and [this](http://stackoverflow.com/questions/27942067/ansi-sql-for-subtracting-a-day#comment44279802_27942067) is exactly what I meant :) – RandomSeed Jan 14 '15 at 12:05
  • row_number() works for Oracle/MS Sql/Postgres but MySQL doesn't support analytics. Note that rownum will often be a lot faster in Oracle than row_number() and doing the ordering slows things up – kayakpim Jan 15 '15 at 08:58
  • @kayakpim Thanks for replies, so is there any other way to provide indexes rowwise in select query ? – FullStack Jan 15 '15 at 09:14
  • 1
    There isn't a universal way (as already mentioned above). There shouldn't really be a need for this. Our ORM (JPA) requires a unique key and so we use rownum-Oracle or row_number() SqlServer with 2 seperated scripts. There are plenty of other ways to do this though - you can hash fields together to create a unique key. Or if it's just for presentation do the row numbering in the presentation layer – kayakpim Jan 15 '15 at 09:54
  • @Kayakpim thank you for your guideline, but can you please give me such example query that can fetch lets say 10 records ? Remember i don't have any unique value column in my database.I am trying to manually assign unique number through select query to fetch 10 records. – FullStack Jan 15 '15 at 10:04

1 Answers1

1

This is how you use the analytic

Sqlserver 

select top 10 row_number() over (order by name)
from sys.objects

Oracle

select row_number() over (order by object_name)
from all_objects
where rownum<=10

Note I've just used the dictionary tables to generate some test data - you can use your own data.

If you want a unique (non-sequential key) you can use guid

Sqlserver 

select top 10 NEWID()
from sys.objects

Oracle

select sys_guid()
from all_objects
where rownum<=10

Sqlserver even has NEWSEQUENTIALID () to create a sequential guid which gets around problems they have indexing a guid for large number of values.

kayakpim
  • 985
  • 2
  • 8
  • 28