Click here to Skip to main content
12,632,326 members (26,660 online)
Rate this:
 
Please Sign up or sign in to vote.
Dear All,

I got bold on the above question in an interview recently. This question i faced many times but yet i have no solution for the question. So please consider the following example and provide me a proper answer of the question this will be a great help.

Example:

table name - emprecord.
first column (primary key)- empid (this id has no pattern like numeric of character-base so consider that their may be anything in this field like 123yuio6yu will be the first id like this )

based on the above things how will i get the last record in this table without using MAX or TOP?
and the database without having an IDENTITY column named ID

Thanks in Advance.
Posted 21-Feb-10 21:17pm
Updated 21-Feb-10 21:37pm
v3
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Dear,
In that scenario we should use dynamic cursor: -

DECLARE @MYVAR NVARCHAR(100)
DECLARE MYTESTCURSOR CURSOR
DYNAMIC 
FOR
SELECT MESSAGE FROM ELMAH_ERROR
OPEN MYTESTCURSOR
FETCH LAST FROM MYTESTCURSOR INTO @MYVAR
CLOSE MYTESTCURSOR
DEALLOCATE MYTESTCURSOR
SELECT @MYVAR

The above code is tested......
  Permalink  
v2
Comments
Member 11665167 8-Jul-15 3:11am
   
I was searching for this for a while and I got your answer but actually,
there is no way to get the last row without using an order by or max with unique column. SQL Server does not internally record something that can be used to decide in what order rows are added.
Check your code in the below link
http://sqlfiddle.com/#!3/b82295/3
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

Hi!

The LAST() Function
The LAST() function returns the last value of the selected column.

SQL LAST() Syntax

SELECT LAST(column_name) FROM table_name


SQL LAST() Example
We have the following "Orders" table:

O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen

Now we want to find the last value of the "OrderPrice" column.

We use the following SQL statement:

SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders

Tip: Workaround if LAST() function is not supported:

SELECT OrderPrice FROM Orders ORDER BY O_Id DESC LIMIT 1

The result-set will look like this:

LastOrderPrice
100
  Permalink  
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

I'd ask the interviewer why they'd expect me to not use the best tool for the job.

If you want to select the last ROW inserted in a TABLE in a DATABASE that has an IDENTITY column named ID, you could use the following:

SELECT *
FROM    TABLE
WHERE  ID = IDENT_CURRENT('TABLE')


is the answer I found with google. perhaps your ability to do basic research is what these interviewers are testing ?
  Permalink  
Comments
deshmukhshivkumar 21-Mar-11 10:10am
   
it will return blank
Member 10925903 23-Dec-14 5:01am
   
this code useful to my project
thanks

...Pradip
ipaldhi 3-Jul-12 9:57am
   
SELECT *
FROM TABLE
WHERE ID = IDENT_CURRENT('TABLE')

this is not work in mysql and oracle
can u give me another solution
arindamrudra 11-Feb-13 1:20am
   
SELECT IDENT_CURRENT ('TABLE_NAME')
Always returning NULL.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

Maybe they meant this keyword : ROWCOUNT , setting it before running a statement will confine the result set size to the provided value in last set rowcount.

So you should have something like this :

set rowcount 1

select * from emprecord order by empid desc


This will give you last employee sorted by their empid.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Use this:

Declare @n int
set @n=1


select empid from emprecord
 where empid not in (
                       select top ((select count(*) from emprecord) - @n ) empid 
                              from emprecord)
  Permalink  
v2
Comments
vinay.sarmalkar 26-Nov-12 11:50am
   
Hello,

Please check the question again.No TOP clause
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 6

Use this:

SELECT IDENT_CURRENT('Employee')

another way:

SELECT ID
FROM    Employee
WHERE  ID = IDENT_CURRENT('Employee')
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.161208.2 | Last Updated 15 Oct 2014
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100