Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Distinct select giving a problem

 
Nischal Topno
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 567
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic