RowID or RowNumber for each rows in SQLServer!

Yesterday when I was interviewing candidates for one of our requirement, I asked a simple select query to write that “how to get, only the last two rows of an Employee table?”

The answer were ( only from few 😦 ) : SELECT TOP 2 * FROM Employee ORDER BY EmployeeId DESC (Provided EmployeeId INT, PrimaryKey)

Then, just i altered the question with “we don’t have an “INT” datatype for EmployeeId column, but UniqueIdentifier (GUID) as well, so now how would you get the last 2 rows?

Some of them were tried to use ROW_NUMBER(), but as that requires OVER(ORDER BY <Column> DESC), and the primary key is with GUID, so they got blocked!

But here is one solution I had, and with that I was asking them: 🙂

SELECT TOP 2 *, %%physloc%% AS PhysLoc FROM Employee ORDER BY PhysLoc DESC

Ref:

http://www.codeproject.com/Articles/159785/Physical-location-of-a-row-in-SQL-Server

 

DENALI (SQLServer 2012):

The special thing is that, we have got the cool and long waiting stuff  “SEQUENCE” in DENALI as it is in ORACLE. That helps great for the above question, with the elegant solution.

Here you go…

CREATE SEQUENCE RowId
    AS int
    START WITH 1
    INCREMENT BY 1 ;

SELECT TOP 2 *, NEXT VALUE FOR RowId AS NewId FROM Employee ORDER BY NewId DESC

Ref:

http://www.sqlservercentral.com/articles/sequence/88422/

http://msdn.microsoft.com/en-us/library/ff878058(v=sql.110).aspx

 

Hope this helps!

Advertisements

2 Comments »

  1. 1

    Laks nice one


RSS Feed for this entry

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: