Click here to Skip to main content
11,482,103 members (68,225 online)
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
Member 10925903 at 23-Dec-14 5:01am
   
this code useful to my project
thanks

...Pradip
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)



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