<div>
what do you understand by &quot;grouped&quot;?<br />
please clarify with data samples.
</div>


<div>
what are you grouping?<br />
<br />
you use group by with aggregates like MAX, MIN, AVG, &nbsp;etc.<br />
<br />
Are you simply trying to make them distinct?<br />
<br />
<br />
SELECT DISTINCT caseguid, clientname, casenumber, referrer<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM pi_active_case_referrals<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE referrer NOT IN(' ')<br />
&nbsp; &nbsp; &nbsp; &nbsp;GROUP BY referrer, clientname, casenumber, caseguid<br />
&nbsp; &nbsp; &nbsp; &nbsp;ORDER BY referrer
</div>


<div>
Since you do not have any aggrates here, such as Sum(), Count(), Min(), etc. in your query, what is the point of a GROUP BY?<br />
<br />

</div>


<div>
oops, &nbsp;I said what I meant, but didn't actually take the &quot;group by&quot; clause out above! &nbsp;<br />
<br />
SELECT DISTINCT caseguid, clientname, casenumber, referrer<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM pi_active_case_referrals<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE referrer NOT IN(' ')<br />
&nbsp; &nbsp; &nbsp; &nbsp;ORDER BY referrer
</div>


<div>
I want it to look like this:<br />
<br />
Referrer &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Clients<br />
Referrer1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Client1<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Client2<br />
Referrer2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Client1<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;client2<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; client3<br />
Referrer3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Client1<br />
Referrer4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; client1<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; client 2
</div>


<div>
&gt;I want it to look like this:<br />
<br />
SELECT DISTINCT referrer, clientname<br />
FROM pi_active_case_referrals<br />
WHERE COALESCE(referrer, ' ') &lt;&gt;&nbsp;' '<br />
ORDER BY referrer, clientname<br />
<br />
Leave the 'blank of duplicate referrans' to whatever reportint tool you will display this with. 
</div>


<div>
That pretty much made things worse. Now I get an error saying that&quot; A field or property with the name 'CaseGUID' was not found on the selected data source.&quot;
</div>


<div>
Also, I added the other fields I had before to it, and it gave me the ID number of the referrer instead of the name, which wasn't doing that before.
</div>


<div>
this is normally not a SQL work, but the client application / reporting tool that can do these much more efficient.<br />
can you confirm that you are using sql server 2005?
</div>


<div>
yes i am using sql server 2005 with visual basic.net
</div>


<div>
ok, let's see:<br />

<pre><code class="code-1 nolinks" id="code-122914"><span>SqlStr = &quot;SELECT case when r = 1 then Referrer else null end Referrer</span>
<span>, CaseGUID, ClientName, CaseNumber</span>
<span>FROM ( select referrer , CaseGUID, ClientName, CaseNumber </span>
<span>       , row_number() over(partition by referrer order by clientName, casenumber) r</span>
<span>        FROM PI_Active_Case_Referrals </span>
<span>         WHERE Referrer NOT IN (' ') </span>
<span>  ) sq</span>
<span>ORDER BY Referrer, clientName, caseNumber&quot;</span>
</code></pre>
</div>


<div>
That gave me a whole lot of errors. Here is the code that I have:<br />
<br />
Imports System.Data.SqlClient<br />
Imports System.Data<br />
<br />
Partial Class Report_Referrals<br />
&nbsp; Inherits System.Web.UI.Page<br />
<br />
&nbsp; Dim conPI As SqlConnection<br />
&nbsp; Dim dtrSelect As SqlDataReader<br />
&nbsp; Dim cmdSelect As SqlCommand<br />
&nbsp; Dim SqlStr As String<br />
<br />
&nbsp; Dim sortorder As String<br />
<br />
&nbsp; Private Property CurrentDataTable() As DataTable<br />
&nbsp; &nbsp; Get<br />
&nbsp; &nbsp; &nbsp; CurrentDataTable = ViewState(&quot;CurrentDataTabl<wbr />e&quot;)<br />
&nbsp; &nbsp; End Get<br />
&nbsp; &nbsp; Set(ByVal value As DataTable)<br />
&nbsp; &nbsp; &nbsp; ViewState(&quot;CurrentDataTabl<wbr />e&quot;) = value<br />
&nbsp; &nbsp; End Set<br />
&nbsp; End Property<br />
<br />
&nbsp; Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load<br />
&nbsp; &nbsp; PIHeader1.cPage = &quot;Tools&quot;<br />
<br />
&nbsp; &nbsp; If Not Page.IsPostBack Then<br />
&nbsp; &nbsp; &nbsp; BindTheData()<br />
&nbsp; &nbsp; End If<br />
&nbsp; &nbsp; Label1.Text = &quot;Referred Clients&quot;<br />
&nbsp; End Sub<br />
<br />
&nbsp; Sub BindTheData()<br />
&nbsp; &nbsp; Dim strconn As String<br />
&nbsp; &nbsp; strconn = System.Configuration.Confi<wbr />gurationMa<wbr />nager.AppS<wbr />ettings(&quot;P<wbr />IConxStrin<wbr />g&quot;)<br />
&nbsp; &nbsp; Dim MyDataSet As DataSet = New DataSet()<br />
<br />
&nbsp; &nbsp; SqlStr = &quot;SELECT DISTINCT referrer, caseguid, clientname, casenumber FROM PI_Active_Case_Referrals WHERE referrer NOT IN (' ') GROUP BY referrer, caseguid, clientname, casenumber ORDER BY referrer&quot;<br />
&nbsp; &nbsp; Dim mydataadapter As SqlDataAdapter = New SqlDataAdapter(SqlStr, strconn)<br />
&nbsp; &nbsp; mydataadapter.Fill(MyDataS<wbr />et, &quot;PI_Active_Case_Referrals&quot;<wbr />)<br />
<br />
&nbsp; &nbsp; ' Lets add another column for the Address<br />
&nbsp; &nbsp; Dim MyDataTable As DataTable = MyDataSet.Tables(&quot;PI_Activ<wbr />e_Case_Ref<wbr />errals&quot;)<br />
<br />
<br />
&nbsp; &nbsp; CurrentDataTable = MyDataTable<br />
&nbsp; &nbsp; DataGrid1.DataSource = MyDataTable<br />
&nbsp; &nbsp; DataGrid1.DataBind()<br />
<br />
&nbsp; End Sub<br />
<br />
End Class<br />

</div>


<div>
&gt;That gave me a whole lot of errors.<br />
what errors?<br />
how did you &quot;use&quot; it?
</div>


<div>
I used it the same way I used the other sql string. I assumed thats what you meant since there was nothing else with that message
</div>


<div>
well, please show the message.<br />
also, try to run the SQL statement directly in the sql, without vb, to check if it works in the first place.
</div>


<div>
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.<br />
<br />
thats the first error after i fix all the syntax stuff
</div>


<div>
you must have used the ORDER BY in the subquery ....
</div>


<div>
I tried it both ways, in the subquery and not..
</div>


<div>
When it isnt in the subquery, i get an error saying that there is a syntax error:<br />
<br />
SqlStr = &quot;SELECT case when r = 1 then Referrer else null end Referrer, CaseGUID, ClientName, CaseNumber FROM (select referrer, CaseGUID, ClientName, CaseNumber, row_number() over(partition by referrer order by clientName, casenumber) FROM PI_Active_Case_Referrals WHERE Referrer NOT IN (' ')) ORDER BY Referrer, ClientName, CaseNumber&quot;<br />
<br />
Please Advise..
</div>


<div>
When I went back and put it in exactly like you had it (with the r and the sq thrown in there) it gave me this error:<br />
<br />
Could not find stored procedure 'False'.
</div>


<div>
i inserted parentheses around &quot;case when r = 1 then Referrer else null end&quot;... that was THAT problem.<br />
<br />
now i get data, but it shows hundreds of blank lines (where there IS a referrer) and one of each of the &nbsp;referrers under it. it isnt grouped by anything, how do i get it to look like this: <br />
<br />
Referrer &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Clients<br />
Referrer1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Client1<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Client2<br />
Referrer2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Client1<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;client2<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; client3<br />
Referrer3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Client1<br />
Referrer4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; client1<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; client 2
</div>


<div>
&gt;When I went back and put it in exactly like you had it (with the r and the sq thrown in there) it gave me this error:<br />
&gt;Could not find stored procedure 'False'.<br />
&quot;impossible&quot;, there is no &quot;False&quot; anywhere?<br />
how do you enter/run the query to test?<br />
<br />
&gt;it isnt grouped by anything, <br />
actually, it SHOULD show the data as you requested.
</div>


<div>
actually, the false was a problem with the sql string you provided. Without the parentheses, it set the entire sql string to false.. Yes, I tested it. I can't post the actual data, but here is what I am getting, and it is NOT what I need:<br />
<br />
Referrer &nbsp; &nbsp; &nbsp; &nbsp;Client<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; John (who has referrer c)<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Sarah (who has referrer d)<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Amy (who has referrer a)<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Adam (who has referrer a)<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Zach (who has referrer b)<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Alice (who has referrer a)<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Dorian (who has referrer b)<br />
referrer a &nbsp;bill<br />
referrer b jack<br />
referrer c mike<br />
etc<br />

</div>


<div>
again:<br />
how do you enter/run the query to test?<br />
<br />
the output you show make me guess that you have a &quot;wrong&quot; order by generated by the query designer...
</div>


<div>
I used the SQL query testing feature in visual studio in my table definitions. Thank you for your time, but this wasn't at all that helpful. I'll award points, but I think we are going to take a different approach
</div>


<div>
That was perfect. Thank you so much!
</div>


<div>
please, before closing, test the above. <br />
the error I made is one I make &quot;often&quot;:<br />
<br />
&lt;...&gt;case when r = 1 then Referrer else null end Referrer&lt;...&gt;<br />
&lt;...&gt;order by referrer&lt;...&gt;<br />
<br />
will order by the resulting value of &quot;referrer&quot;, and not the original one.<br />
changing the order by to:<br />
&lt;...&gt;order by sq.referrer&lt;...&gt;<br />
<br />
so specifing that the order by has to take the original subquery value should do the ordering trick.<br />
<br />

</div>


<div>
Thank you for your patience and time!
</div>


<div>
<div class="content wysiwyg-content">
I have a query that I need to execute to get the referrer of our clients. I want to group them by the referrer, but when I try to add a where clause to take out the blank referrals, it ignores the group by clause. If I add an order by clause to sort the referrals, it ignores the group by clause as well. Here is my SQL statement:<br />
<br />
&nbsp;SqlStr = &quot;SELECT CaseGUID, ClientName, CaseNumber, Referrer &nbsp;FROM PI_Active_Case_Referrals WHERE Referrer NOT IN (' ') Group By Referrer, ClientName, CaseNumber, CaseGUID ORDER BY Referrer&quot;<br />
<br />
The result is that I don't have any blank referrals and they are in order, but they are not grouped. Any help is appreciated.
</div>
</div>


I have a query that I need to execute to get the referrer of our clients. I want to group them by the referrer, but when I try to add a where clause to take out the blank referrals, it ignores the group by clause. If I add an order by clause to sort the referrals, it ignores the group by clause as well. Here is my SQL statement:

 SqlStr = "SELECT CaseGUID, ClientName, CaseNumber, Referrer  FROM PI_Active_Case_Referrals WHERE Referrer NOT IN (' ') Group By Referrer, ClientName, CaseNumber, CaseGUID ORDER BY Referrer"

The result is that I don't have any blank referrals and they are in order, but they are not grouped. Any help is appreciated.

Problems with SQL Statement- Group By, Order By, and Sort Order

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.