row-number

Row numbers for a query in informix

i am using informix database, i want a query which you could also generate a row number along with the query Like select row_number(),firstName,lastName from students; row_number(),firstName,lastName 1 john mathew 2 ricky pointing 3 sachin tendulkar here firstName,lastName are from Database , where as row number is genera...

MSSQL Paging is returning random rows when not supposed too

I'm trying to do some basic paging in MSSQL. The problem I'm having is that I'm sorting the paging on a row that (potentially) has similar values, and the ORDER BY clause is returning "random" results, which doesn't work well. So for example. If I have three rows, and I'm sorting them by a "rating", and all of the ratings are = '5' - t...

MSSQL Select statement with incremental integer column... not from a table

Hi guys I need, if possible, a t-sql query that, returning the values from an arbitrary table, also returns a incremental integer column with value = 1 for the first row, 2 for the second, and so on. This column does not actually resides in any table, and must be strictly incremental, because the ORDER BY clause could sort the rows of ...

How can I speed up row_number in Oracle?

I have a SQL query that looks something like this: SELECT * FROM( SELECT ..., row_number() OVER(ORDER BY ID) rn FROM ... ) WHERE rn between :start and :end Essentially, it's the ORDER BY part that's slowing things down. If I were to remove it, the EXPLAIN cost goes down by an order of magnitude (over 1...

SQL Query With Row_Number, order by and where clause.

I have the following SQL querry: select      ID, COLUMN1, COLUMN2 from      (select ID, COLUMN1, COLUMN2, row_number() over (order by 2 DESC) NO from A_TABLE) where      NO between 0 and 100 What I am trying to do is to select the first 100 records of the query select ID, COLUMN1, COLUMN2 from ATABLE order by 2 DESC And here are the ...

SubSonic and Paging using ROW_NUMBER ()

How to enable ROW_NUMBER () with SubSonic to do paging? ...

Equivalent of Oracle's RowID in SQL Server

What's the equivalent of Oracle's RowID in SQL Server? ...

How do I use ROW_NUMBER() in SQL?

Hi, I want to use the ROW_NUMBER() to get... To get the max(ROW_NUMBER()) --> Or i guess this would also be the count of all rows I tried doing: SELECT max(ROW_NUMBER() OVER(ORDER BY UserId)) FROM Users but it didn't seem to work... To get ROW_NUMBER() using a given piece of information, ie. if I have a name and I want to kno...

Referencing a MySQL sub-select in another query

I have a basic MySQL table, terms, comprised of an id and term field. I want to create an alphabetically sorted dictionary index (in the literal sense), that would list ten 10 terms above the selected term, and 20 below it. An example of this could be found here http://www.urbandictionary.com/define.php?term=GD2&defid=3561357 where ...

LINQ-to-objects index within a group + for different groupings (aka ROW_NUMBER with PARTITION BY equivalent)

After much Google searching and code experimentation, I'm stumped on a complex C# LINQ-to-objects problem which in SQL would be easy to solve with a pair of ROW_NUMBER()...PARTITION BY functions and a subquery or two. Here's, in words, what I'm trying to do in code-- the underlying requirement is removing duplicate documents from a lis...

Should SQL ranking functionality be considered as "use with caution"

This question originates from a discussion on whether to use SQL ranking functionality or not in a particular case. Any common RDBMS includes some ranking functionality, i.e. it's query language has elements like TOP n ... ORDER BY key, ROW_NUMBER() OVER (ORDER BY key), or ORDER BY key LIMIT n (overview). They do a great job in increas...

SQL Server 2005 - Row_Number()

Hi All, I'm trying to understand the unusual behaviour seen when ordering results in a descending order using the row_number() function when using a DISITINCT on the outermost select in my query as below: SELECT DISTINCT (ID), State_Id, Name_Of_Trip, Date_Of_Travel, Creation_Date, Locking_Id, Applicant_Name, Reference_Number, Stat...

ROW_NUMBER() and nhibernate - finding an item's page

given a query in the form of an ICriteria object, I would like to use NHibernate (by means of a projection?) to find an element's order, in a manner equivalent to using SELECT ROW_NUMBER() OVER (...) to find a specific item's index in the query. (I need this for a "jump to page" functionality in paging) any suggestions? NOTE: I don't...

Paging and custom-ordering a result

Hello, I want to query a table so that it´s ordered the following way: 1) "entry" 2) "entry#" 3) "entry something" 4) "..entry.." I did this via Union All and 4 different queries. But additionally, I want to include paging, so that I can f.ex. receive the row-numbers 1-100, 101-200 etc. I tried Row_Num() Over (Order By) but did not ge...

SQL Server: join on derived table that contains WITH clause?

I'd like to join on a subquery / derived table that contains a WITH clause (the WITH clause is necessary to filter on ROW_NUMBER() = 1). In Teradata something similar would work fine, but Teradata uses QUALIFY ROW_NUMBER() = 1 instead of a WITH clause. Here is my attempt at this join: -- want to join row with max StartDate on JobModel...

Why does ROW_NUMBER OVER (ORDER BY column) return a different result order than just ORDER BY column?

I'm on SQL Server 2008, using NHibernate as persistence layer (although this problem is purely SQL, I believe). I've boiled down my problem to the following SQL statement: SELECT TOP 2 this_.Id as Id36_0_, this_.Name as Name36_0_, ROW_NUMBER() OVER (ORDER BY this_.IsActive) as MyOrder FROM Campsites this_ ORDER BY this...

Row_Number() CTE Performance when using ORDER BY CASE

I have a table I'd like to do paging and ordering on and was able to get a query similar to the following to do the work (the real query is much more involved with joins and such). WITH NumberedPosts (PostID, RowNum) AS ( SELECT PostID, ROW_NUMBER() OVER (ORDER BY CASE WHEN @sortCol = 'User' THEN User END DESC, CASE ...

how to Add Row numbers for the result set for the below query and get specific records

SELECT Cases.pKey, Cases.Name, Cases.FileNum, MIN(cases.[Lot/Unit]) as FirstOfLotUnit, MIN(Cases.AcctNum)AS FirstOfAcctNum, MIN(Cases.CaseNum) AS FirstOfCaseNum, MIN(Cases.Address1) AS FirstOfAddress1, MIN(Cases.Balance) AS FirstOfBalance, MIN(CaseActList.Date) AS FirstOfDate FROM Cases LEFT JOIN CaseActList ON Cases.pKey = C...