School Assignment? Project Due Tomorrow? Chat LIVE With A Programming Expert!

Programming Help

Web Development



What's Here?

  • Members: 300,503
  • Replies: 826,159
  • Topics: 137,488
  • Snippets: 4,420
  • Tutorials: 1,148
  • Total Online: 1,889
  • Members: 89
  • Guests: 1,800

Welcome to Dream.In.Code
Become an Expert!

Join 300,503 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,889 people online right now. Registration is fast and FREE... Join Now!




Last Index ID

 

Last Index ID
Ukec

11 Jun, 2009 - 03:36 AM
Post #1

New D.I.C Head
*

Joined: 1 Jun, 2009
Posts: 6
Hello,

I have problems with a SQL database. I am using MS SQL Server 2008 and I would like to get a last inserted index from one table.
I need to get a last index in a table. I have an ID which incrementaly increases by 1 (is Identity).

Problem is if I write:

SELECT MAX(ID) FROM dbo.Table

I get i.e. 15. But than I delete the 15th record and now I need to get last index which is 15, but I get 14 with the upper command.
Can anyone help me on this?
Thank you

User is offlineProfile CardPM
+Quote Post


RudiVisser

RE: Last Index ID

11 Jun, 2009 - 03:41 AM
Post #2

.. does not guess solutions
Group Icon

Joined: 5 Jun, 2009
Posts: 1,873



Thanked: 137 times
Dream Kudos: 125
Expert In: PHP, MySQL, HTML, CSS, C#

My Contributions
I don't understand your issue.

If you have 15 records in your database you want to get 15, but if you have 14 records in your database you also want to get 15?

You can use SCOPE_IDENTITY() to get the last identity value inserted, if that's what you're looking for.
User is online!Profile CardPM
+Quote Post

Trogdor

RE: Last Index ID

11 Jun, 2009 - 04:08 AM
Post #3

D.I.C Addict
Group Icon

Joined: 6 Oct, 2006
Posts: 619



Thanked: 14 times
Dream Kudos: 200
My Contributions
after you do the insert you can use select @@identity
User is offlineProfile CardPM
+Quote Post

Ukec

RE: Last Index ID

11 Jun, 2009 - 04:41 AM
Post #4

New D.I.C Head
*

Joined: 1 Jun, 2009
Posts: 6
Ok my problem is that I have Win Form and one label which has to show the last index in table dbo.Orders.
In the tabel dbo.Orders I have one IDOrders which is Identity and incrementally increases by 1 each time one record is inserted.
So if I have 15 records in that tabel when I want to get the last index I need to get 15.
But if I delete one record from that tabel there will be 14 records in that tabel but becose of the IDOrders is identity with Identity Increment by 1 I need to get the last index 15 not 14.
So this is my problem. I need to get last index event if there is a gap (someone has deleted one or two records).
Because with SELECT MAX(IDOrders) FROM dbo.Orders would show me 14 not 15 in my example.
I have tried with select @@identity and I get NULL values.
User is offlineProfile CardPM
+Quote Post

RudiVisser

RE: Last Index ID

11 Jun, 2009 - 04:43 AM
Post #5

.. does not guess solutions
Group Icon

Joined: 5 Jun, 2009
Posts: 1,873



Thanked: 137 times
Dream Kudos: 125
Expert In: PHP, MySQL, HTML, CSS, C#

My Contributions
@@identity is the same as SCOPE_IDENTITY() but have you tried it?

SQL
SELECT SCOPE_IDENTITY() FROM dbo.Table


You shouldn't get NULL from that query..
User is online!Profile CardPM
+Quote Post

Ukec

RE: Last Index ID

11 Jun, 2009 - 04:58 AM
Post #6

New D.I.C Head
*

Joined: 1 Jun, 2009
Posts: 6
Yes I tried.
I get something like this

--+ ID |
1 | NULL |
2 | NULL |
3 | NULL |
4 | NULL |
5 | NULL |
6 |... |

Can something be wrong with my table? I am a beginner in SQL but I think I have done everything fine.
User is offlineProfile CardPM
+Quote Post

noorahmad

RE: Last Index ID

11 Jun, 2009 - 05:15 AM
Post #7

Webmaster
Group Icon

Joined: 12 Mar, 2009
Posts: 2,018



Thanked: 125 times
Dream Kudos: 1350
My Contributions
ok then try this:
SQL
SELECT ISNULL(MAX(ID),0)+1 FROM TABLENAME

User is offlineProfile CardPM
+Quote Post

Ukec

RE: Last Index ID

11 Jun, 2009 - 12:15 PM
Post #8

New D.I.C Head
*

Joined: 1 Jun, 2009
Posts: 6
yeah what I get here is the right answer but what if I delete one more record.
Then the number will be 14 not 15.

You see I need to track the last index which was inserted even if there was some of the records deleted.
If that is not possible I would understand but it has to possible. How than does MS SQL knows which is the last ID number, because if I insert new record into the table MS SQL would continue from 15th record.

I am sorry about my english. smile.gif
Thanks for help anyway!
User is offlineProfile CardPM
+Quote Post

mikeblas

RE: Last Index ID

12 Jun, 2009 - 05:38 AM
Post #9

D.I.C Regular
Group Icon

Joined: 8 Feb, 2008
Posts: 390



Thanked: 27 times
My Contributions
I think that IDENT_CURRENT() does what you want.
User is offlineProfile CardPM
+Quote Post

Ukec

RE: Last Index ID

12 Jun, 2009 - 08:18 AM
Post #10

New D.I.C Head
*

Joined: 1 Jun, 2009
Posts: 6
QUOTE(mikeblas @ 12 Jun, 2009 - 05:38 AM) *

I think that IDENT_CURRENT() does what you want.


YES! icon_up.gif
That is exactly what I needed! Thank you very much! biggrin.gif
User is offlineProfile CardPM
+Quote Post

Fast ReplyReply to this topicStart new topic

Time is now: 11/8/09 05:03AM
Invision Power Board v2.1.7 © 2009  IPS, Inc.
Licensed to: MediaGroup1

Live Help!

Be Social

Dream.In.Code RSS Feed Dream.In.Code LinkedIn Group Follow Us On Twitter Fan Us On Facebook

Tutorials

Programming

Web Development

Reference Sheets

Code Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month