How to Get the Unique ID for the Last Inserted Row

[Khanjan]

hi,

I want to get the last three ids that are inserted into the database, where ids are auto-increment.

When i try to do this:
SELECT MAX(nieuwsid) AS nieuwsid FROM nieuws;
i get the highst id.

To get the last three ids, i want to store this highst id in a variable. Later i want to do this:

second=lastid -1;
third =second -1;

My problem is, how can i store the highes id into a variable.

In a situation like this i know how to do this:
java.sql.ResultSet result = statement.executeQuery(sql);

while(result.next())
{
String nieuwsid = result.getString("nieuwsid");}%>

But in the highesd id case, i dont know. Please help,
Thanx in advance

 

[siberian]

Assuming you are using mysql you can just select the 3 highest ID's and assume they are the 'last' IDs since ID's are allocated sequential in an auto-increment

select ID from YOURTABLE order by ID DESC limit 0,3;

Replace ID and YOURTABLE with your info. That query will return the 3 highest numbered IDs and do it properly since there are circumstances where you can have auto-increment gaps.

 

[Khanjan]

siberian Thank you for your reply. I want to show the 3 newst news on my page. When i store news in mine database(Mysql), i want to show juist three newst news articles not all of them but juist the newst three articles.

By doing what i am doing, i cant do it. Can anyone tell me how to do it????

Here is the code:

<%
Class.forName("com.mysql.jdbc.Driver");
java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:mysql://localhost/afghan?user=fahim&password=fahim");
java.sql.Statement statement = connection.createStatemen();

String sql ="select nieuwsid from nieuws order by nieuwsid DESC limit 0,3";

java.sql.ResultSet result = statement.executeQuery(sql);

while(result.next())
{
String nieuwsid = result.getString("nieuwsid");
String titel = result.getString("titel");
String content = result.getString("content");
String auteur = result.getString("auteur");
String datum = result.getString("datum");
String nieuwstype = result.getString("nieuwstype");
%>

<TABLE cellSpacing=0 cellPadding=0 width=407 border=0>
<TBODY>
<TR>
<TD> <IMG height=20 alt="" src="images/tops.jpg"></TD>
</TR>
<TR>
<TD width=407 height=10 class="bordor-line" class="new">
<%= titel%>
</TD>
</TR>
<TR>
<TD class="bordor-line" vAlign=top width=407 height=10>
<%= content%>
</TD>
</TR>
<TR>
<TD class="bordor-line" vAlign=top width=407 >
<%= auteur%>
</TD>
</TR>
<TR>
<TD>
<img height=20 alt="" src="images/down.jpg" >
</TD>
</TR>
</TBODY>
</TABLE>
<%}%>

 

[siberian]

What error are you getting? A quick glance through at 2:36AM and it looks ok to me.

 

[Khanjan]

Hi,

I have found the probleem.
Instead of select * , i did select nieuwsid, that is why i could not show the news articles on my page.

Thank you siberian for your help,