I only need the two most current items in a result set and was wondering what the best way to do that would be without a break. I realize that rs.next() returns true or false and tried to stop it with a counter but that failed. I have this at the moment:
while(rs.next()){
String name = rs.getString("name");
String startTime = rs.getString("starting_time");
String endTime = rs.getString("ending_time");
String date = rs.getString("directory");
String loc = rs.getString("location");
htmlBuilder.append("<li><a href='public/committees/calendar'>"+ name+"<br>");
htmlBuilder.append(date +" "+startTime+" - "+endTime+"</a> <!-- Link/title/date/start-end time --><br>");
htmlBuilder.append("<strong>Location: </strong>"+loc+"<br>");
htmlBuilder.append("</li>");
}
html = htmlBuilder.toString();
As you can tell, this returns everything from the ResultSet but I only need the first two entries.
Here is my correct query:
SELECT to_char(to_date(to_char(x.starting_date), 'J'),'mm/dd/yyyy') as start_date, to_char(to_date(to_char(x.ending_date), 'J'),'mm/dd/yyyy') as end_date, to_char(to_date(to_char(x.starting_date), 'J'),'yyyy-mm-dd') as directory, x.starting_time, x.ending_time, x.description, x.description as location, x.name, x.short_name, x.add_info_url, x.contact_name, x.contact_info FROM calitem x, calendar x, calitemtypes x WHERE x.calendar_id = x.calendar_id AND x.short_name LIKE ? AND x.style_id = 0 AND x.starting_date > to_char(sysdate-1, 'J') AND x.item_type_id = x.item_type_id AND ROWNUM <= 3 ORDER BY to_date(to_char(x.starting_date), 'J')
Adding the rownum attribute worked perfectly and the query was ordered before return. Thanks for the help
You should limit the query to return only the two most current rows. This can by achieved by a LIMIT clause (exists in MySQL, not sure about other DBs) and an ORDER BY clause.
There is not need to add an index that would count the rows returned by the ResultSet.