Hi anish! I’m sorry you are experiencing this difficulty! This issue has been escalated internally and we’re working on getting the support you need. We appreciate your patience & hope to have an expert answer your inquiry soon.
I think you are saying that adding the ORDER BY on one of the grouping columns causes results to be filtered out, is that right?
Please post the full result of both queries as text, and don’t cut off the result of the second query.
Please provide a JSON document with the full query profile. Profile each query, then do SHOW PROFILE JSON, cut and paste that into a file, and send it. Or, download the profile using SingleStore Studio.
And provide the SingleStore version number you are running with (select @@memsql_version).
Yes Hanson , with order by clause I am getting proper grouping based on gender.
singlestore> select @@memsql_version;
±-----------------+
| @@memsql_version |
±-----------------+
| 7.6.7 |
±-----------------+
1 row in set (0.00 sec)
I don’t understand what you mean. Please reply with the full output for both queries.
I am just highlighting here the query output nature from MySQL & SingleStore . I have created the same tables in both databases . and the No. of records and data returning is same . But the grouping is not happening properly in Singlestore . I want to understand the reason for that , whether this is a common behavior of distributed database . I have attached the query result from both MySQL and SingleStore , I hope you can understand the difference from the attached screenshot .
Oh, I understand now. Thanks.
The result of a query with GROUP BY don’t have to be displayed in any specific order according to the SQL Standard. If you want the results to be sorted, you should add an ORDER BY clause.
The MySQL result set is sorted by dname but that may be just a coincidence, because perhaps it used a sort-merge-based group by algorithm. It is not required.