Retrieving last 8 rows in a table

[Sean Casey]

What statement could I use to retrieve the last 8 rows in a table?
It would be easier if I could select the last row
and then the last row -1 etc.
any ideas???

[Michael Fitzmaurice]

Hi
Use a loop where you get the last row (as explained in response to your query about that), then (in a loop) subtract 1 from the value of the artID you used to get that row and get the matching row for that artID. Repeat 6 more times. To do it this way, you must be sure that the artID fields are all sequential and ordered, i.e. there is no possibility that one of them has been deleted (e.g. artID 10, 9, and 7 exist, but 8 has been deleted - problems ensue).
It would probably be best to write a small stored procedure to return you a resultset containing the last 8 rows only. But you could do it all by executing 8 Statement.execute() calls, if you really want.
------------------
"One good thing about music - when it hits, you feel no pain"
Bob Marley
[This message has been edited by Michael Fitzmaurice (edited September 12, 2001).]

[Suji N]

check the following code....which was done on using a w t
it will be verymuch useful
if u got any pblm...tell me
i will solve it
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
public class UserSearch extends Panel implements ActionListener, TextListener
{
Label totalrecords, userIDLbl,userNameLbl,title, label1, label2, label3, label4, label5;
TextField userIDTxt,userNameTxt;
Button previous,search,nextPage,reset,exit;
TextField tf[][];
int i,recPos,recCount;
boolean nextP,prev,ent;

static MyConnection myConus1;
Statement st,st1,st2;
ResultSet rs,rs2;

UserSearch()
{
myConus1 = new MyConnection();
try
{
st = myConus1.con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
st1 = myConus1.con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
st2 = myConus1.con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
}
catch(Exception e)
{
System.out.println("excep in defining my con objects "+e);
}
tf=new TextField[6][5];
userIDLbl = new Label();
userNameLbl = new Label();
userNameTxt = new TextField(3);
userIDTxt = new TextField(3);
title = new Label();
search = new Button();
previous = new Button();
nextPage = new Button();
reset = new Button();
exit = new Button();
label1=new Label();
label2=new Label();
label3=new Label();
label4=new Label();
label5=new Label();
totalrecords=new Label();
recPos = 1;
userIDTxt.setCursor(java.awt.Cursor.getPredefinedCursor(java.awt.Cursor.TEXT_CURSOR));
add(userIDTxt);
userIDTxt.setFont(new Font("DialogInput", Font.PLAIN, 17));
userIDTxt.setBounds(120,60,108,26);
userNameTxt.setCursor(java.awt.Cursor.getPredefinedCursor(java.awt.Cursor.TEXT_CURSOR));
add(userNameTxt);
userNameTxt.setFont(new Font("DialogInput", Font.PLAIN, 17));
userNameTxt.setBounds(348,60,108,26);

search.setLabel("Search");
add(search);
search.setBackground(java.awt.Color.lightGray);
search.setBounds(480,60,93,24);

setLocation(295,115); // width and height
setSize(570,508);// size of the window
setVisible(true);
setLayout(null);
userNameLbl.setText("UserName");
add(userNameLbl);
userNameLbl.setBounds(264,60,66,28);

add(totalrecords);
totalrecords.setForeground(java.awt.Color.blue);
totalrecords.setFont(new Font("DialogInput", Font.PLAIN, 12));
totalrecords.setBounds(175,483,280,24);
userIDLbl.setText("UserID");
userIDLbl.setBounds(48,60,60,28);
add(userIDLbl);

title.setText("User Search");
add(title);
title.setForeground(java.awt.Color.blue);
title.setFont(new Font("Dialog", Font.BOLD, 18));
title.setBounds(252,24,115,19);
previous.setLabel("Previous");
add(previous);
previous.setBackground(java.awt.Color.lightGray);
previous.setBounds(84,444,93,26);
nextPage.setLabel("NextPage");
add(nextPage);
previous.setEnabled(false);
nextPage.setBackground(java.awt.Color.lightGray);
nextPage.setBounds(444,444,93,26);
nextPage.setEnabled(false);
reset.setLabel("Reset");
add(reset);
reset.setBackground(java.awt.Color.lightGray);
reset.setBounds(264,444,93,26);
exit.setLabel("Exit");
//add(exit);
exit.setBackground(java.awt.Color.lightGray);
exit.setBounds(432,466,93,26);
label1.setText("UserID");
add(label1);
label1.setBounds(55,118,58,22);
label1.setForeground(java.awt.Color.blue);
label1.setFont(new Font("DialogInput", Font.PLAIN, 15));

label2.setText("Password");
add(label2);
label2.setBounds(150,118,74,22);
label2.setForeground(java.awt.Color.blue);
label2.setFont(new Font("DialogInput", Font.PLAIN, 15));

label3.setText("Name");
add(label3);
label3.setBounds(270,118,50,22);
label3.setForeground(java.awt.Color.blue);
label3.setFont(new Font("DialogInput", Font.PLAIN, 15));

label4.setText("Authority");
add(label4);
label4.setBounds(355,118,82,22);
label4.setForeground(java.awt.Color.blue);
label4.setFont(new Font("DialogInput", Font.PLAIN, 15));

label5.setText("Citizen-Code");
add(label5);
label5.setBounds(460,118,110,22);
label5.setForeground(java.awt.Color.blue);
label5.setFont(new Font("DialogInput", Font.PLAIN, 15));

search.addActionListener(this);
nextPage.addActionListener(this);
reset.addActionListener(this);
exit.addActionListener(this);
previous.addActionListener(this);
userIDTxt.addTextListener(this);
userNameTxt.addTextListener(this);

previous.setEnabled(false);
nextPage.setEnabled(false);
int xPos;
for(int i=0;i<6;i++)
{
xPos=1;
for(int j=0;j<5;j++)
{
tf[i][j] = new TextField();
tf[i][j].setFont(new Font("DialogInput", Font.PLAIN, 17));
tf[i][j].setVisible(false);
tf[i][j].setEditable(false);
add(tf[i][j]);

if(j==2)
{
tf[i][j].setBounds(48*xPos,50*(i+3),115,25);//from left //gap of tf between row to row // gap starting between tfs in row //height of tf //i+3 = starting of tf columns from height
xPos = xPos+1;
}
else if(j==3)
{
tf[i][j].setBounds(63*xPos,50*(i+3),40,25);
xPos = xPos+1;
}
else if(j==4)
{
tf[i][j].setBounds(65*xPos,50*(i+3),135,25);//from left //gap of tf between row to row // gap starting between tfs in row //height of tf //i+3 = starting of tf columns from height
xPos = xPos+1;
}
else
{
tf[i][j].setBounds(48*xPos,50*(i+3),85,25);
xPos = xPos+2;
}
}
}
}
/*
Starting of both nextpage and previous button methods
*/
public void displayRecords()
{
//nextP=true;
for(int i=0;i<6;i++)
{
for(int j=0;j<5;j++)
{
tf[i][j].setVisible(false);
}
}
show();
try
{
if(recPos==1)
previous.setEnabled(false);
rs.absolute(recPos);
i=0;
do
{
if (rs.isLast())
{
nextPage.setEnabled(false);
}
for(int j=0;j<5;j++)
{
tf[i][j].setText(rs.getString(j+1));
tf[i][j].setVisible(true);
}
i++;
}while (i<6 && rs.next());
}
catch(Exception e)
{
System.out.println("Exc in displayrecords() "+e);
}
invalidate();
show();
}
/*
End of both nextpage and previous button methods
*/
public void actionPerformed(ActionEvent ae)
{
String sqlString=null;

if(ae.getSource()==search)
{
for(int i=0;i<6;i++)
{
for(int j=0;j<5;j++)
{
tf[i][j].setVisible(false);
}
}
nextPage.setEnabled(true);
previous.setEnabled(false);
if(!(userIDTxt.getText().equals("") && userNameTxt.getText().equals("")))
{
sqlString = "select * from users where userid like '" + userIDTxt.getText()+ "%' and name like '" + userNameTxt.getText() + "%'";
}
if(!(userIDTxt.getText().equals("")) && userNameTxt.getText().equals(""))
{
sqlString = "select * from users where userid like '" + userIDTxt.getText()+ "%'";
}
if(userIDTxt.getText().equals("") && !(userNameTxt.getText().equals("")))
{
sqlString = "select * from users where name like '" + userNameTxt.getText()+ "%'";
}
if(userIDTxt.getText().equals("") && userNameTxt.getText().equals(""))
{
sqlString = "select * from users";
}
try
{
recCount=0;
recPos=1;
if(nextP)
{
rs.close();
nextP = false;
}
rs = st.executeQuery(sqlString);

while(rs.next())
{
recCount++;
}

totalrecords.setText("Total No of Records Found "+recCount);
System.out.println("Record Count" + recCount);
if(recCount<6)
{
previous.setEnabled(false);
nextPage.setEnabled(false);
}
if(recCount<1)
{
System.out.println("No Data Found ");
(new NoData(new Frame(), true)).setVisible(true);
System.out.println("No Data Found ");
}
nextP=true;
displayRecords();
}
catch(Exception e)
{
System.out.println("in search method "+e);
}
//nextPage.setEnabled(true);
System.out.println(sqlString);
}
if(ae.getSource()==nextPage)
{
previous.setEnabled(true);
recPos = recPos+6;
displayRecords();
show();
}
if(ae.getSource()==previous)
{
nextPage.setEnabled(true);
recPos = recPos-6;
displayRecords();
show();
}
if(ae.getSource()==reset)
{
//new UserModify();
previous.setEnabled(false);
nextPage.setEnabled(false);
totalrecords.setText("");
for(int i=0;i<6;i++)
{
for(int j=0;j<5;j++)
{
tf[i][j].setVisible(false);
}
}
userNameTxt.setText("");
userIDTxt.setText("");
}
if(ae.getSource()==exit)
{
closeconnection();
setVisible(false);
}
}
public static void main(String args[])
{
new UserSearch();
}
public static void closeconnection()
{
try
{
myConus1.con.close();
System.out.println("Con Closed");
}
catch(Exception eno)
{
//System.out.println(eno);
}
} //closeconnection();
public void keyPressed(KeyEvent ke)
{
//System.out.println(ke);
}

public void keyReleased(KeyEvent ke)
{
}

public void keyTyped(KeyEvent ke)
{
}

public void textValueChanged(TextEvent te)
{
if(te.getSource()==userIDTxt)
if(userIDTxt.getText().length()>=8)
userIDTxt.transferFocus();
if(te.getSource()==userNameTxt)
if(userNameTxt.getText().length()>=30)
userNameTxt.transferFocus();
}
}

[Jamie Robertson]

1. if using oracle:
"select x,y from table order by rownum desc"
then you can iterate through the first 8 rows(which are actually the last 8 rows)
2. if you have a timestamp field in your table:
"select x,y from table order by timestampfield desc"
then you can iterate through the first 8 rows
3. if have no other alternative(you will have to select all the records and only use the last 8!)

Statement s = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs = s.executeQuery("select x,y from table");
if (rs.next())
{
rs.afterLast();
for (int i=0;i<8;i++){
rs.previous();
String id = rs.getString(1);
...etc
}
}

for 1 and 2 you can statement.setMaxRows(8);
Jamie

[This message has been edited by Jamie Robertson (edited September 13, 2001).]

[Jamie Robertson]

4. If you have a primary key that is autonumber or some type of incrementing number then you could:
"select x,y from table order by id desc"
then you can iterate through the first 8 rows and again setMaxRows(8).
Jamie

[Pranit Saha]

Hi jamie..
Thanks for ur sugession.. it has helped me a lot to solve my problem..
Pranit..

Originally posted by Jamie Robertson:
4. If you have a primary key that is autonumber or some type of incrementing number then you could:
"select x,y from table order by id desc"
then you can iterate through the first 8 rows and again setMaxRows(8).
Jamie