Click here to Skip to main content
11,649,522 members (75,941 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 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
Comments
Member 11665167 at 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 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)

  Print Answers RSS
0 OriginalGriff 354
1 F-ES Sitecore 344
2 Kornfeld Eliyahu Peter 319
3 CPallini 290
4 Maciej Los 269
0 OriginalGriff 1,577
1 Sergey Alexandrovich Kryukov 1,087
2 CPallini 1,015
3 DamithSL 996
4 jyo.net 994


Advertise | Privacy | Mobile
Web03 | 2.8.150804.4 | 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