How can I sort counted DataTable rows according to specific parameter

user_16600932 0
Viewed 164

I am trying to use dataTable.Rows.Count, but sort the result based on a specific parameter. That parameter being "Column1" in my DataTable. So that the output gives me the rows pertaining to that distinct value only. My DataTable is in a View, but I need to use the sorted int values in a ViewModel.

I am able to count the rows with public static int o { get; set; } and a dt.Rows.Count in my DataTable. I then grab the value by instantiating my View in my ViewModel, with int numberOfRows = ViewName.o;.

But that gives me the total number of rows, whereas I need the number of rows per distinct value in "Column1".

My question is, where and how can I do the required sorting? Because when I've gone as far as to count them and add them to an int (in my ViewModel), there's no way to know what row they used to represent, right? And If I try to sort in the DataTable (in the View) somehow, I don't know how to reference the distinct values. They might vary from time to time as the program is used, so I can't hard-code it.

Comment suggested using query instead, adding my stored procedure for help to implement solution:

SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[myProcedure]
    @param myUserDefinedTableType readonly

AS
BEGIN TRANSACTION
    INSERT INTO [dbo].[myTable] (/* list of columns */)
        SELECT [Column1], /* this is the column I need to sort by */
               -- more columns
               -- the rest of the columns
        /* I do aggregations to my columns here, I am adding several thousands row to 20 summarized rows, so after this line, I can no longer get ALL the rows per "Column1", but only the summarized rows. How can I count the rows BEFORE I do the aggregations? */
        FROM @param

        GROUP BY [Column2], [Column1]
        ORDER BY [Column2], [Column1]

// Some UPDATE clauses

COMMIT TRANSACTION
datatable sorting c#
2 Answers

I believe the wisest choice is to act in the db side.
Assuming you're using SQL Server, the query should be:

SELECT *, COUNT(*) OVER (PARTITION BY Column1) AS c
FROM Table1
ORDER BY c

This query returns the data on your table "Table1", plus the column "c" that represents the count of the value of "Column1", per each row.
Finally, it sorts rows by the column "c", as you request.

EDIT

To complete this task, I will use a Common Table Expression:

-- Code before INSERT...

;WITH CTE1 AS (  
    SELECT *,  
        COUNT(*) OVER (PARTITION BY [Column1]) AS c  
    FROM @param  
)  
INSERT INTO [dbo].[myTable] (/* list of columns - must add the column c */)  
SELECT [Column1],  
       [Column2],  
       [c],  
       -- aggregated columns    
FROM CTE1  
GROUP BY [Column2], [Column1], c  
      
-- Code after INSERT...

In the Common Table Expression "CTE1" I select all the values in @param, adding a column "c" with the count per Column1.

Note: if you have 5 rows with the same value of Column1, but two different values in Column2, in myTable you will have two rows (because of the GROUP BY [Column2], [Column1]), both with c=5.
If you want instead obtain the count grouped by Column1 and Column2, you have to declare c as follows: COUNT(*) OVER (PARTITION BY [Column1], [Column2]) AS c.

I hope I was clear, If not I'm available to explain it in a different way.

EXAMPLE

CREATE TABLE myTable (col1 VARCHAR(50), col2 INT, col3 INT, c INT) 

CREATE TYPE myUserDefinedTableType AS TABLE (column1 VARCHAR(50), column2 INT,  column3 INT)  
DECLARE @param myUserDefinedTableType  
INSERT INTO @param VALUES ('A', 1, 4), ('A', 2, 3), ('A', 2, 6), ('B', 2, 3)  

;WITH CTE1 AS (
    SELECT *, COUNT(*) OVER (PARTITION BY [Column1]) AS c 
    FROM @param
)  
INSERT INTO [myTable]([col1], [col2], [col3], [c])  
SELECT [column1], [column2], 
    -- aggregated columns  
    MAX([column3]), 
    -- count
    [c]  
FROM CTE1  
GROUP BY [column2], [column1], c
user_152456730 
DataView dv = dt.DefaultView;
dv.Sort = "SName ASC"; -- your column name
DataTable dtsorted = dv.ToTable();

 DataTable dtsorted = dv.ToTable(true, "Sname","Surl" ); //return distinct rows
user_13640560
Related