Click here to Skip to main content
Rate this: bad
good
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 20:17pm
Edited 21-Feb-10 20: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
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 at 21-Mar-11 10:10am
   
it will return blank
ipaldhi at 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 at 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 at 26-Nov-12 11:50am
   
Hello, Please check the question again.No TOP clause

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 sanket saxena 355
1 Abhinav S 303
2 Sergey Alexandrovich Kryukov 299
3 OriginalGriff 225
4 thatraja 220
0 Sergey Alexandrovich Kryukov 8,497
1 OriginalGriff 4,850
2 Peter Leow 3,839
3 Maciej Los 3,535
4 Er. Puneet Goel 3,107


Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 10 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid