Feature Request: Select Distinct Values For A Column

[cmm324]

Hi everyone, I have been using SqlYog for a few months now and it is growing on me. I have used MySql Workbench for a long time but have been fed up with its instability and lack of support for feature requests and even worse, issues. They never even responded when I posted a bug report for whenever I open a database connection, even if I do not run any queries, the application spikes one of my CPU cores and holds it there.

 

However, this feature request is for the object explorer and/or the info tab. I often want to see the distinct values for a certain column when working with new data structures. In object explorer, it would be great to be able to right click a column and select an option that opens a new query tab with a SELECT DISTINCT statement on that column and then is auto run to show the results. For the info tab, a simple link or button for each column line in the table to select distinct.

What would make this even better, is to have an alternate option that does a group by counting the occurrences of each distinct value in that column in the database table (select column, count(*) as column_count from … group by).

 

Thanks,

 

Chris

[peterlaursen]

Thanks for your interest in SQLyog.

 

I will not comment on your experiences with Workbench. Over the 10+ years we have co-existed with GUI tools from MySQL/Sun/Oracle as well as from others, we have avoided ‘mud-throwing’ at each others.  And we are not the ones who will be changing that.

 

It is also very unlikely that we will do any changes to the INFO tab as proposed. This is mainly for perfomance reasons.  Currently INFO tab is populated by fast running SHOW queries (see yourself in the HISTORY tab after selecting the INFO tab what the queries are).  What you propose could cause the INFO tab to populate very slowly with large data. All depending on what indexes exist and the storage engine used it could take several minutes (and ‘several’ could be ’20’ or more).

 

What remains is then proposals for the Object Browser.  If I understand you propose more content in the context menu of a column object here.  This context menu could have entries added like

* NOT NULL count

* NULL count

* DISTINCT count

* (more?) 

.. what would then paste a query to the editor that would discover the selected option.

 

 

Please confirm that I understood and if I overlooked something. 

[peterlaursen]

Actually there could be a single option (‘column statistics’) returning

 

1) total rows

2) NULL rows

3) NOT NULL rows

4) DISTINCT rows

 

.. for the column. Maybe for indexes we could then add a option for “index cardinality” in the context menu.

[peterlaursen]

Please consider this query:

SELECT 

  COUNT(*) AS '#rows',

  COUNT(`columnname`) AS '#rows NOT NULL',

  COUNT(DISTINCT `columnname`) AS '#DISTINCT rows' 

FROM `tablename`;

 

Isn’t this basically what you are looking for (without the need to type any tablename or columnname yourself)?

[cmm324]

Hi, sorry for the delay in responding and I completely understand your position re:workbench.

The values from the query you provided could be useful but I was looking for something simpler like this:

 

SELECT

DISTINCT columnname as distinct_values,

COUNT(*) as occurrences

FROM tablename

GROUP BY distinct_values;

I find myself doing this often when working with table structures that make use of codes or integers for relation tables, like an id or code for a status column.

[sathish]

Hello,

 

Apologies for the delay in replying. We have added this request in our issue tracker here: https://code.google.com/p/sqlyog/issues/detail?id=2047 but currently this is not in our road map for another few months.

 

Regards,

Sathish

[Author]

Posts