Click here to Skip to main content
15,867,965 members
Please Sign up or sign in to vote.
5.00/5 (1 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
Updated 5-Dec-19 18:24pm
v3

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

SQL
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......
 
Share this answer
 
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
Hi!

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

SQL LAST() Syntax

SQL
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:

SQL
SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders

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

SQL
SELECT OrderPrice FROM Orders ORDER BY O_Id DESC LIMIT 1

The result-set will look like this:

LastOrderPrice
100
 
Share this answer
 
v2
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:

SQL
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 ?
 
Share this answer
 
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.
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 :

SQL
set rowcount 1

select * from emprecord order by empid desc


This will give you last employee sorted by their empid.
 
Share this answer
 
Use this:

SQL
Declare @n int
set @n=1


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

Please check the question again.No TOP clause
Use this:

SELECT IDENT_CURRENT('Employee')

another way:

SQL
SELECT ID
FROM    Employee
WHERE  ID = IDENT_CURRENT('Employee')
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900