number of rows returned by SELECT LAST(column)

I have about 50,000 rows in my MS Acess database table so I have used SELECT LAST(column) AS newField FROM table... to retrieve the last data in the column however when I check the number of rows in the resultset using

resultset.last();

int rowcount = rs.getRow();

rowcnt returns the total no.rows (about 50,000) in the table.

I thought it should just return one.

Is it normal?

[416 byte] By [Booh1a] at [2007-11-27 5:26:42]

«« java.lang.NoClassDefFoundError: MyClass (wrong name: mypackage/MyClass)
»» how to write a single bit to an output device

# 1

You haven't done anything to restrict the number of rows returned, so yes it's normal. Add a where clause.

dcmintera at 2007-7-12 14:47:44 >

top of Java-index,Database Connectivity,Java Database Connectivity (JDBC)...

# 2

Thank you dcminta!the statement below worked!!!"SELECT * FROM database WHERE id = (SELECT MAX(id) FROM database)";

Booh1a at 2007-7-12 14:47:44 >

# 3

> Thank you dcminta!

> the statement below worked!!!

> "SELECT * FROM database WHERE id = (SELECT MAX(id)

> FROM database)";

This seems a poor way to get a row count. Are you trying to determine how many rows are in your table? If so then use SELECT COUNT(*) FROM table. Selecting the MAX id to get the row count is not a good idea.

cotton.ma at 2007-7-12 14:47:44 >

# 4

I think he's just trying to get the most recently inserted row in the table. His solution seems over-verbose though - I'd have thought the following would be sufficient:select col1, col2, ... from table1 where id = max(id);

dcmintera at 2007-7-12 14:47:44 >

# 5

Thanks again dcminta. I'll try with your code.

I just wanted to know why resultset returned the number of all records in that column when I only selected the last.

I had the 揑nvalid Cursor Position?error with "while(rs.next())" as I (thought I) had only one record in the resultset and I was fiddling with my code.

They are all fine now.

Thanks guys.

Booh1(old lady)

Booh1a at 2007-7-12 14:47:44 >

# 6

If it works it works. Glad to hear it. Yours may well optimise down to exactly the same thing anyway, though I (biased) think mine is easier to read.

dcmintera at 2007-7-12 14:47:44 >

# 7

> I think he's just trying to get the most recently> inserted row in the table.I see. I think I got confused between the oddness of the query and the subject line. Well never mind me then.

cotton.ma at 2007-7-12 14:47:44 >

Database Connectivity

Java Database Connectivity (JDBC)

Java Database Connectivity (JDBC)

Java programming resources: FAQs, tutorials, compiler and browser download sites, documentation, books lists, IDEs, etc.

number of rows returned by SELECT LAST(column)

Database Connectivity Hot Topic

Database Connectivity

All Classified