You should write a group by query on BillStation field
This is my Procedure
ALTER PROC [dbo].[usp_BillRegister_Search]
@FromDate DATETIME
,@ToDate DATETIME
,@FromPlace VARCHAR(60)
,@ToPalce VARCHAR(60)
AS
BEGIN
SET NOCOUNT ON
SELECT
BillNo
,Convert(VARCHAR(10), @FromDate,103) AS FromDate
,Convert(VARCHAR(10), @ToDate,103) AS ToDate
,Convert(VARCHAR(10), BillDate,103) AS BillDate
,(SELECT CentreName FROM RegionMaster WHERE RegionMasterID = BillingStation) AS BillingStation
,(SELECT AccountName FROM FinAccountsMaster WHERE AccountID = PartyCode) AS PartyCode
,SUM(M.TotalFreight) over(partition by BillingStation) GroupTotalFreight
,SUM(M.TotalBillAmt) over(partition by BillingStation) GroupTotalBillAmt
,SUM(M.Tds) over(partition by BillingStation) GroupTds
,SUM(M.BillAmtCleared) over(partition by BillingStation) GroupBillAmtCleared
,SUM(M.Deductions) over(partition by BillingStation) GroupDeductions
FROM BillsMaster M
WHERE M.BillDate BETWEEN @FromDate AND @ToDate
AND M.BillingStation IN (SELECT R.RegionMasterID FROM RegionMaster R WHERE R.CentreName LIKE @FromPlace + '%' OR R.CentreName = @FromPlace)
AND M.PartyCode IN (SELECT F.AccountID FROM FinAccountsMaster F WHERE F.AccountName LIKE @ToPalce + '%' OR F.AccountName = @ToPalce)
ORDER BY BillingStation
SET NOCOUNT OFF
END
I bound Group values for my GridView. But here i want only one row to be bound
you just showed one sample data(Of gridview) and not the actual output
it can be like this also,
Delhi
3 Delhi 50 25 50 100
3(no idea) Delhi 150 125 150 150
See this both,they are not equal.
i) Either correct from query itself.
ii)If you don't want to disturb the query ,if it is correct then,you can in the end,insert the output in table variable.and query on table variable using distinct or group by which suit it.
ramakrishna4...
Member
77 Points
111 Posts
How to select only one row from DataSet
Oct 06, 2010 12:21 PM|LINK
Hi,
My DataSet gives a result like this when i bind to GridView
BillNo BillStation Amt1 Amt2 Amt3 Amt4
Hyd
1 Hyd 100 50 50 25
2 Hyd 100 50 50 25
Bang
2 Bang 500 100 200 50
2 Bang 500 100 200 50
Delhi
3 Delhi 50 25 50 100
3 Delhi 50 25 50 100
As the rows from each group is same. I want to display only the first row from each group.
Thank you.
dataset gridview
malav_rajend...
Member
146 Points
35 Posts
Re: How to select only one row from DataSet
Oct 06, 2010 12:33 PM|LINK
What is your sql query?
You should write a group by query on BillStation field
"Asp.net 2.0"
Rajendra Malav
http://rajendramalav.blogspot.com
Please Mark as Answered If post is helpful.
ramakrishna4...
Member
77 Points
111 Posts
Re: How to select only one row from DataSet
Oct 06, 2010 12:39 PM|LINK
This is my Procedure
ALTER PROC [dbo].[usp_BillRegister_Search] @FromDate DATETIME ,@ToDate DATETIME ,@FromPlace VARCHAR(60) ,@ToPalce VARCHAR(60) AS BEGIN SET NOCOUNT ON SELECT BillNo ,Convert(VARCHAR(10), @FromDate,103) AS FromDate ,Convert(VARCHAR(10), @ToDate,103) AS ToDate ,Convert(VARCHAR(10), BillDate,103) AS BillDate ,(SELECT CentreName FROM RegionMaster WHERE RegionMasterID = BillingStation) AS BillingStation ,(SELECT AccountName FROM FinAccountsMaster WHERE AccountID = PartyCode) AS PartyCode ,SUM(M.TotalFreight) over(partition by BillingStation) GroupTotalFreight ,SUM(M.TotalBillAmt) over(partition by BillingStation) GroupTotalBillAmt ,SUM(M.Tds) over(partition by BillingStation) GroupTds ,SUM(M.BillAmtCleared) over(partition by BillingStation) GroupBillAmtCleared ,SUM(M.Deductions) over(partition by BillingStation) GroupDeductions FROM BillsMaster M WHERE M.BillDate BETWEEN @FromDate AND @ToDate AND M.BillingStation IN (SELECT R.RegionMasterID FROM RegionMaster R WHERE R.CentreName LIKE @FromPlace + '%' OR R.CentreName = @FromPlace) AND M.PartyCode IN (SELECT F.AccountID FROM FinAccountsMaster F WHERE F.AccountName LIKE @ToPalce + '%' OR F.AccountName = @ToPalce) ORDER BY BillingStation SET NOCOUNT OFF ENDI bound Group values for my GridView. But here i want only one row to be bound
KumarHarsh
All-Star
15155 Points
3658 Posts
Re: How to select only one row from DataSet
Oct 07, 2010 06:51 AM|LINK
you just showed one sample data(Of gridview) and not the actual output
it can be like this also,
Delhi
3 Delhi 50 25 50 100
3(no idea) Delhi 150 125 150 150
See this both,they are not equal.
i) Either correct from query itself.
ii)If you don't want to disturb the query ,if it is correct then,you can in the end,insert the output in table variable.and query on table variable using distinct or group by which suit it.
Kumar Harsh
ramakrishna4...
Member
77 Points
111 Posts
Re: How to select only one row from DataSet
Oct 07, 2010 07:20 AM|LINK
Thank you Harsh,
I got the result by using Distinct in my Query.
SELECT DISTINCT BillStation, SUM(Amt1), SUM(Amt2), SUM(Amt3), SUM(Amt4) FROM TableName
I think my explanation is wrong in my question post.