Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL2000 SQL-server-2005 SQL , +
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
Edited 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
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
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
0 OriginalGriff 230
1 PIEBALDconsult 150
2 DamithSL 125
3 BillWoodruff 108
4 Garth J Lancaster 90
0 OriginalGriff 5,790
1 DamithSL 4,601
2 Maciej Los 4,012
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,195


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 15 Oct 2014
Copyright © CodeProject, 1999-2014
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