This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Distinct select giving a problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Distinct select giving a problem" Watch "Distinct select giving a problem" New topic
Author

Distinct select giving a problem

Nischal Topno
Ranch Hand

Joined: Nov 24, 2001
Posts: 45
Hello,
Say a field (AFIELD) in my table (MYTABLE) contains the following data:
AFIELD
======
NOKIA
NOKIA
SAMSUNG
When i create a ResultSet(rs) using the following query,
SELECT AFIELD FROM MYTABLE
the rs.next() returns true for three times (which is correct).
But when i create a ResultSet(rs) using the following query,
SELECT DISTINCT AFIELD FROM MYTABLE
still the rs.next() returns true for three times (which is wrong). Also it gives an "Invalid cursor state" error when trying to fetch the the record for the third time using getString("AFIELD").
Please let me know what's going wrong and where?
I am using the java 1.3.1_02-b02 JDK. The statement is (ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY).
Please advice.
Nischal
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 565
That sounds seriously wrong. What data do you see when you do the query direct in the db command line?
Try this query and see what you get as well:
SELECT afield, COUNT(*) FROM mytable
GROUP BY afield

Adam


I have seen things you people would not believe, attack ships on fire off the shoulder of Orion, c-beams sparkling in the dark near the Tennhauser Gate. All these moments will be lost in time, like tears in the rain.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Distinct select giving a problem
 
Similar Threads
rs.next() returns true, but if (rs.next()) block not executed
Result set type
some advice please
JdbcOdbcDriver/Stored procedures. Please help
Strange JDBC PreparedStatements Problem