• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Retrieving last 8 rows in a table

 
Ranch Hand
Posts: 625
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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???
 
Ranch Hand
Posts: 168
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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).]
 
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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();
}
}
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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!)


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

[This message has been edited by Jamie Robertson (edited September 13, 2001).]
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 130
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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


reply
    Bookmark Topic Watch Topic
  • New Topic