none
c# String Query to find top 10 Repeated records in database RRS feed

  • Question

  • I have asked this question before but didn’t explain correctly what was needed. I will try again please bear with me.

    I have an access database and I want to look at one column and count how many times a number is repeated, sort highest to lower order. The code I have so far will show the top 10 rows. I need it to count and if it’s a repeated entry to sum it and list it high to low.

     private void btn_Top_10_Click(object sender, EventArgs e)
            {
                string queryString = "SELECT SheetID, Part FROM ILC";
                queryString = "select top 10 Part,Part from ILC";
                loadDataGrid(queryString);
    }

    data base on left, results I'm getting so far on right.

    It should show 123 is repeated 5 times and should be at the top.

    the number 1 is repeated 3 times and should be next and so on.

    Thanks



    Booney440



    • Edited by Booney440 Monday, September 3, 2018 6:28 PM update question
    Sunday, September 2, 2018 6:11 PM

Answers

  • The basics

    using System.Collections.Generic;
    
    namespace WindowsFormsApp1.Classes
    {
        public class ContactData
        {
            public string ContactType { get; set; }
            public List<int> Identifiers { get; set; }
            public override string ToString()
            {
                return ContactType;
            }
        }
    }
    

    Then

    public List<ContactData> LoadTopCustomerTitles()
    {
        var results = new List<ContactData>();
        var titleList = new List<string>();
    
        using (var cn = new OleDbConnection {ConnectionString = _fileName.BuildConnectionString()})
        {
            using (var cmd = new OleDbCommand {Connection = cn})
            {
                cmd.CommandText = "SELECT TOP 10 " + 
                                    "ContactTitle,COUNT(ContactTitle) As TitleCount " + 
                                    "FROM Customers " + 
                                    "GROUP BY ContactTitle " +
                                    "Order by COUNT(ContactTitle) DESC";
    
                cn.Open();
                var reader = cmd.ExecuteReader();
    
                while (reader.Read())
                {
                    titleList.Add(reader.GetString(0));
                }
    
                reader.Close();
    
                cmd.Parameters.Add(new OleDbParameter() { OleDbType = OleDbType.LongVarChar});
    
                foreach (var title in titleList)
                {
                    cmd.CommandText = "SELECT Identifier FROM Customers WHERE ContactTitle =?;";
                    cmd.Parameters[0].Value = title;
    
                    reader = cmd.ExecuteReader();
    
                    var dItem = new ContactData() { ContactType = title, Identifiers = new List<int>()};
    
                    while (reader.Read())
                    {
                        dItem.Identifiers.Add(reader.GetInt32(0));
                    }
    
                    reader.Close();
                    results.Add(dItem);
                }
            }
        }
    
        return results;
    }

    Results in a debug session stopped on the line "return results"


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Booney440 Tuesday, September 4, 2018 8:54 PM
    Tuesday, September 4, 2018 7:17 PM
    Moderator

All replies

  • Try this query,

    SELECT count(Part) as count, Part
    FROM table
    GROUP BY ILC
    ORDER BY count;



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. profile for Sajeetharan at Stack Overflow, Q&A for professional and enthusiast programmers


    Sunday, September 2, 2018 6:38 PM
  • I need it to work from Button click.

    Booney440

    Sunday, September 2, 2018 7:07 PM
  • Try this query too:

       SELECT TOP 10 Part

       FROM ILC

       GROUP BY Part

       ORDER BY COUNT(*) DESC;


    Sunday, September 2, 2018 7:15 PM
  • are the 2 above codes for MS Access Database? I need it to work as my above code in C#.

    Booney440


    • Edited by Booney440 Monday, September 3, 2018 12:17 AM update
    Sunday, September 2, 2018 7:23 PM
  • Hi Booney440,

    For your question, how do you put the 10 part numbers in order? In your description, you want to do something like below.

    123
    123
    123
    123
    123
    1
    1
    1
    …
    …

    But the way you used to order, I  do not find the regular pattern. Could you provide more details?

    Here is the code I used to get the 10 part numbers from the access database in C#. Dur to you do not provide the code about loadDataGrid method. I show the data in datagridview for reference.

     private void button1_Click_1(object sender, EventArgs e)
            {
                dataGridView1.AllowUserToAddRows = false;
                string strDSN = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = Database1.accdb";
                string strSQL = "SELECT Part FROM Table1 WHERE id<=10";// ORDER BY Part
                // create Objects of ADOConnection and ADOCommand  
                OleDbConnection myConn = new OleDbConnection(strDSN);
                OleDbDataAdapter myCmd = new OleDbDataAdapter(strSQL, myConn);
                myConn.Open();
                DataSet dtSet = new DataSet();
                myCmd.Fill(dtSet, "Table1");
                DataTable dTable = dtSet.Tables[0];
                dataGridView1.DataSource = dtSet.Tables["Table1"].DefaultView;
                myConn.Close();
            }

    You could try the sql string below to order by the part number.

    string strSQL = "SELECT Part FROM Table1 WHERE id<=10 ORDER BY Part";

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, September 3, 2018 3:23 AM
    Moderator
  • Wendy

    I need to put the numbers in order by how many times they are repeated.

    x number was repeated 50 times

    y number was repeated 43 times

    z number was repeated 25 times

    Group and sort highest to lowest

    I load the DataGrid like this.

     public Form1()
            {
    
                InitializeComponent();
                // iniciate DB connection
                string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ILCHotSheet.mdb";
                try
                {
    
                    database = new OleDbConnection(connectionString);
                    database.Open();
                    //SQL query to list ILC
                    string queryString = "SELECT HotSheetID, ToDay, Part, Timeord, Timerec, sdock, LCCN, Requestor, Notes, Type, Shift, RunOutTime, CICSTYPE FROM ILC,Reasontype WHERE Reasontype.typeID = ILC.typeID";
                    loadDataGrid(queryString);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    return;
                }
            }
            #endregion
    
            #region Load dataGrid
            public void loadDataGrid(string sqlQueryString)
            {
    
                OleDbCommand SQLQuery = new OleDbCommand();
                DataTable data = null;
                dataGridView1.DataSource = null;
                SQLQuery.Connection = null;
                OleDbDataAdapter dataAdapter = null;
                dataGridView1.Columns.Clear(); // <-- clear columns
                //---------------------------------
                SQLQuery.CommandText = sqlQueryString;
                SQLQuery.Connection = database;
                data = new DataTable();
                dataAdapter = new OleDbDataAdapter(SQLQuery);
                dataAdapter.Fill(data);
                dataGridView1.DataSource = data;
                dataGridView1.AllowUserToAddRows = false; // remove the null line
                dataGridView1.ReadOnly = true;
                dataGridView1.Columns[0].Visible = false;
                for (var i = 1; i < data.Columns.Count - 1; i++)
                {
    
                }
    
    
                //
                // insert edit button into datagridview
                editButton = new DataGridViewButtonColumn();
                editButton.HeaderText = "Edit";
                editButton.Text = "Edit";
                editButton.UseColumnTextForButtonValue = true;
                editButton.Width = 80;
                dataGridView1.Columns.Add(editButton);
                // insert delete button to datagridview
                deleteButton = new DataGridViewButtonColumn();
                deleteButton.HeaderText = "Delete";
                deleteButton.Text = "Delete";
                deleteButton.UseColumnTextForButtonValue = true;
                deleteButton.Width = 80;
                dataGridView1.Columns.Add(deleteButton);
            }


    Booney440

    Monday, September 3, 2018 2:54 PM
  • Here is an example where ContactType is to your Part, see if this is what you want. Note I excluded the buttons. 

    As shown I selected a row in the DataGridView, code sniffs for Order Administrator, finds two and shows the related company primary key and company name. For in the wild the id field would be hidden. Pressing edit or delete button provides the id for either edit or removal.

    I can upload a VS2017 solution to OneDrive with the MS-Access database if the following suits your needs.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, September 4, 2018 12:27 PM
    Moderator
  • Karen 

    Thank you for looking at this.

    In the example Owner should be listed first it repeats 6 times in the column.

    Sales Rep should be next it is listed 5 times in the column and so on to get the Top 10 repeaters.

    This is what it looks like in Access


    Booney440

    Tuesday, September 4, 2018 2:38 PM
  • Not following "Owner" is not shown in the grid nor is "Sales Rep", I see "Part" and "Num".

    Can you see how the following would be incorrect for you?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, September 4, 2018 3:59 PM
    Moderator
  • sorry

    I was comparing your grid to mine, But what you are showing above it what i need to accomplish How would I write that in c#?

     


    Booney440

    Tuesday, September 4, 2018 4:25 PM
  • The basics

    using System.Collections.Generic;
    
    namespace WindowsFormsApp1.Classes
    {
        public class ContactData
        {
            public string ContactType { get; set; }
            public List<int> Identifiers { get; set; }
            public override string ToString()
            {
                return ContactType;
            }
        }
    }
    

    Then

    public List<ContactData> LoadTopCustomerTitles()
    {
        var results = new List<ContactData>();
        var titleList = new List<string>();
    
        using (var cn = new OleDbConnection {ConnectionString = _fileName.BuildConnectionString()})
        {
            using (var cmd = new OleDbCommand {Connection = cn})
            {
                cmd.CommandText = "SELECT TOP 10 " + 
                                    "ContactTitle,COUNT(ContactTitle) As TitleCount " + 
                                    "FROM Customers " + 
                                    "GROUP BY ContactTitle " +
                                    "Order by COUNT(ContactTitle) DESC";
    
                cn.Open();
                var reader = cmd.ExecuteReader();
    
                while (reader.Read())
                {
                    titleList.Add(reader.GetString(0));
                }
    
                reader.Close();
    
                cmd.Parameters.Add(new OleDbParameter() { OleDbType = OleDbType.LongVarChar});
    
                foreach (var title in titleList)
                {
                    cmd.CommandText = "SELECT Identifier FROM Customers WHERE ContactTitle =?;";
                    cmd.Parameters[0].Value = title;
    
                    reader = cmd.ExecuteReader();
    
                    var dItem = new ContactData() { ContactType = title, Identifiers = new List<int>()};
    
                    while (reader.Read())
                    {
                        dItem.Identifiers.Add(reader.GetInt32(0));
                    }
    
                    reader.Close();
                    results.Add(dItem);
                }
            }
        }
    
        return results;
    }

    Results in a debug session stopped on the line "return results"


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Booney440 Tuesday, September 4, 2018 8:54 PM
    Tuesday, September 4, 2018 7:17 PM
    Moderator
  • Excellent,

    I used some code you have posted and it is working.

    Thank you for your help.

     private void btn_Top_10_Click(object sender, EventArgs e)
            {
    
                string queryString = "SELECT SheetID, Part FROM ILC";
                queryString = "SELECT TOP 10 " +
                                    "Part,Part As PartNumber" +
                                    "Part,COUNT(Part) As PartCount " +
                                    "FROM ILC " +
                                    "GROUP BY Part " +
                                    "Order by COUNT(Part) DESC";
                loadDataGrid(queryString);


    Booney440

    Tuesday, September 4, 2018 8:54 PM
  • Good to hear you have what is needed to continue forward :-)

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, September 4, 2018 9:12 PM
    Moderator