I am using query given below in hibernate with oracle10g,

"SELECT OcspTransactionLogs.relyingPartyIp,OcspTransactionLogs.relyingPartyId,count(OcspTransactionLogs.id)
FROM OcspTransactionLog OcspTransactionLogs
WHERE OcspTransactionLogs.responseStatus = 'successful'
GROUP BY OcspTransactionLogs.relyingPartyIp,OcspTransactionLogs.relyingPartyId
ORDER BY count(OcspTransactionLogs.id) desc"

It properly grouping records without "order by clause",as I order it by count(...), It fill first record data in "OcspTransactionLogs.relyingPartyIp and OcspTransactionLogs.relyingPartyId" for the subsequent records,

e.g,

True grouping records are as,

192.123.12.13 N/A 10
192.123.12.13 testData 30

But when order by clause is used as in above query, records will be,

192.123.12.13 N/A 30
192.123.12.13 N/A 10

What can I do to get exact records by using order by clause,

Needs your urgent help.

Thanks.
Naeem

1. Determine who is the culprint.

run the sql statement in a the sql tool of your choice directly against the database. Preferably sqlplus. Check if the problem shows up. If yes continue under oracle.

Otherwise continue under Hibernate


Oracle: Throw away any Index on the affected tables, rebuild the table, recreate the indexes and try again. If it doesn't fix the problem contact oracle support.


Hibernate: Post the exact code resulting in the query (both variations).
Enable debugging to see the exact sql statements issued and the bind parameters/results returned.
Post your findings here

In oracle client this query working fine,

If I run query in application using Hibernate 3.0

"SELECT OcspTransactionLogs.relyingPartyIp,OcspTransactionLogs.relyingPartyId,count(OcspTransactionLogs.id)
FROM OcspTransactionLog OcspTransactionLogs
WHERE OcspTransactionLogs.responseStatus = 'successful'
GROUP BY OcspTransactionLogs.relyingPartyIp,OcspTransactionLogs.relyingPartyId"

The above query working fine (shows true results).

AS I add following order by clause to above query then it do order but it duplicate the results in all records for fields included in group by clause.

ORDER BY count(OcspTransactionLogs.id) desc

It seems there is some issue of hibernate with oracle10g.

Thanks,

Best Regards,
Naeem.

Post the exact code resulting in the query (both variations).
Enable debugging to see the exact sql statements issued and the bind parameters/results returned.
Post your findings here