Error on query (Select distinct)

I am not an expert in sql but I am trying to learn. I have what for me is a
complex query, but I am getting an error when sorting out by one of the
parameters, here is the error:
Technical Information (for support personnel)
a.. Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must appear
in the select list if SELECT DISTINCT is specified.
/BlueDot2005Beta/Intranet/Reports/Advanced/ReportsResults.asp, line 155
--
Line 155 is the end of the recordset (When it executes)
<%
Dim CasesAdvancedReports
Dim CasesAdvancedReports_numRows
Set CasesAdvancedReports = Server.CreateObject("ADODB.Recordset")
CasesAdvancedReports.ActiveConnection = MM_eimmigration_STRING
CasesAdvancedReports.Source = "SELECT distinct a.id,a.caseid, b.userid,
b.FirstNm, b.LastNm, e.Processcatalog, b.I94DateD,
a.ExpDate,a.maincase,a.caserelationship, f.MaidenNm, a.approvaldate FROM
Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid INNER JOIN
Processcatalog AS E ON A.process = E.processcatalogid LEFT JOIN Users
AS F ON A.empid = F.userid LEFT JOIN Activities AS G ON A.id =
G.caseid AND G.acttype = 'history' AND G.lastmodified IS NOT NULL
AND G.dateinitiated IS NULL WHERE A.firmid = " +
Replace(CasesAdvancedReports__MMColParam
, "'", "''") + " AND a.archived LIKE
'" + Replace(CasesAdvancedReports__MMColParam
3, "'", "''") + "' ORDER BY "
+ Replace(CasesAdvancedReports__MMColParam
2, "'", "''") + ""
setQueryBuilderSource CasesAdvancedReports, WAQueryBuilder1_whereClause,
false
CasesAdvancedReports.CursorType = 0
CasesAdvancedReports.CursorLocation = 2
CasesAdvancedReports.LockType = 1
CasesAdvancedReports.Open()
CasesAdvancedReports_numRows = 0
%>
This is my query:
SELECT distinct a.id,a.caseid, b.userid, b.FirstNm, b.LastNm,
e.Processcatalog, b.I94DateD, a.ExpDate,a.maincase,a.caserelationship,
f.MaidenNm, a.approvaldate
FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid INNER
JOIN Processcatalog AS E ON A.process = E.processcatalogid LEFT JOIN
Users AS F ON A.empid = F.userid LEFT JOIN Activities AS G ON A.id =
G.caseid AND G.acttype = 'history' AND G.lastmodified IS NOT NULL
AND G.dateinitiated IS NULL
WHERE A.firmid = MMColParam AND a.archived LIKE 'MMColParam3'
ORDER BY MMColParam2
Any help is appreciated.
AleksJust as the error says -- you need to include the column you're ordering by
in the select list if you use the DISTINCT keyword. If this ruins your
distinctness, you will have to re-write query so as not to use DISTINCT. If
you need help with that, please post DDL and sample data. See this link for
more info:
http://www.aspfaq.com/etiquette.asp?id=5006
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:O5eKN2dEFHA.4052@.TK2MSFTNGP09.phx.gbl...
> I am not an expert in sql but I am trying to learn. I have what for me is
a
> complex query, but I am getting an error when sorting out by one of the
> parameters, here is the error:
> --
> Technical Information (for support personnel)
> a.. Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must
appear
> in the select list if SELECT DISTINCT is specified.
> /BlueDot2005Beta/Intranet/Reports/Advanced/ReportsResults.asp, line 155
> --
> Line 155 is the end of the recordset (When it executes)
> <%
> Dim CasesAdvancedReports
> Dim CasesAdvancedReports_numRows
> Set CasesAdvancedReports = Server.CreateObject("ADODB.Recordset")
> CasesAdvancedReports.ActiveConnection = MM_eimmigration_STRING
> CasesAdvancedReports.Source = "SELECT distinct a.id,a.caseid, b.userid,
> b.FirstNm, b.LastNm, e.Processcatalog, b.I94DateD,
> a.ExpDate,a.maincase,a.caserelationship, f.MaidenNm, a.approvaldate FROM
> Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid INNER JOIN
> Processcatalog AS E ON A.process = E.processcatalogid LEFT JOIN Users
> AS F ON A.empid = F.userid LEFT JOIN Activities AS G ON A.id =
> G.caseid AND G.acttype = 'history' AND G.lastmodified IS NOT NULL
> AND G.dateinitiated IS NULL WHERE A.firmid = " +
> Replace(CasesAdvancedReports__MMColParam
, "'", "''") + " AND a.archived
LIKE
> '" + Replace(CasesAdvancedReports__MMColParam
3, "'", "''") + "' ORDER BY
"
> + Replace(CasesAdvancedReports__MMColParam
2, "'", "''") + ""
> setQueryBuilderSource CasesAdvancedReports, WAQueryBuilder1_whereClause,
> false
> CasesAdvancedReports.CursorType = 0
> CasesAdvancedReports.CursorLocation = 2
> CasesAdvancedReports.LockType = 1
> CasesAdvancedReports.Open()
> CasesAdvancedReports_numRows = 0
> %>
>
>
> --
> This is my query:
> SELECT distinct a.id,a.caseid, b.userid, b.FirstNm, b.LastNm,
> e.Processcatalog, b.I94DateD, a.ExpDate,a.maincase,a.caserelationship,
> f.MaidenNm, a.approvaldate
> FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid INNER
> JOIN Processcatalog AS E ON A.process = E.processcatalogid LEFT JOIN
> Users AS F ON A.empid = F.userid LEFT JOIN Activities AS G ON A.id
=
> G.caseid AND G.acttype = 'history' AND G.lastmodified IS NOT NULL
> AND G.dateinitiated IS NULL
> WHERE A.firmid = MMColParam AND a.archived LIKE 'MMColParam3'
> ORDER BY MMColParam2
>
> --
> Any help is appreciated.
> Aleks
>|||Like the error message says if you are going to use an order by clause
with distinct then the order by items MUST appear in the select
portion of the statement.
On Sun, 13 Feb 2005 10:06:07 -0500, "Aleks" <arkark2004@.hotmail.com>
wrote:

>I am not an expert in sql but I am trying to learn. I have what for me is a
>complex query, but I am getting an error when sorting out by one of the
>parameters, here is the error:
>--
>Technical Information (for support personnel)
> a.. Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must appear
>in the select list if SELECT DISTINCT is specified.
> /BlueDot2005Beta/Intranet/Reports/Advanced/ReportsResults.asp, line 155
>--
>Line 155 is the end of the recordset (When it executes)
><%
>Dim CasesAdvancedReports
>Dim CasesAdvancedReports_numRows
>Set CasesAdvancedReports = Server.CreateObject("ADODB.Recordset")
>CasesAdvancedReports.ActiveConnection = MM_eimmigration_STRING
>CasesAdvancedReports.Source = "SELECT distinct a.id,a.caseid, b.userid,
>b.FirstNm, b.LastNm, e.Processcatalog, b.I94DateD,
>a.ExpDate,a.maincase,a.caserelationship, f.MaidenNm, a.approvaldate FROM
>Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid INNER JOIN
>Processcatalog AS E ON A.process = E.processcatalogid LEFT JOIN Users
>AS F ON A.empid = F.userid LEFT JOIN Activities AS G ON A.id =
>G.caseid AND G.acttype = 'history' AND G.lastmodified IS NOT NULL
>AND G.dateinitiated IS NULL WHERE A.firmid = " +
> Replace(CasesAdvancedReports__MMColParam
, "'", "''") + " AND a.archived LIK
E
>'" + Replace(CasesAdvancedReports__MMColParam
3, "'", "''") + "' ORDER BY "
>+ Replace(CasesAdvancedReports__MMColParam
2, "'", "''") + ""
>setQueryBuilderSource CasesAdvancedReports, WAQueryBuilder1_whereClause,
>false
>CasesAdvancedReports.CursorType = 0
>CasesAdvancedReports.CursorLocation = 2
>CasesAdvancedReports.LockType = 1
>CasesAdvancedReports.Open()
>CasesAdvancedReports_numRows = 0
>%>
>
>
>--
>This is my query:
>SELECT distinct a.id,a.caseid, b.userid, b.FirstNm, b.LastNm,
>e.Processcatalog, b.I94DateD, a.ExpDate,a.maincase,a.caserelationship,
>f.MaidenNm, a.approvaldate
>FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid INNER
>JOIN Processcatalog AS E ON A.process = E.processcatalogid LEFT JOIN
>Users AS F ON A.empid = F.userid LEFT JOIN Activities AS G ON A.id
=
>G.caseid AND G.acttype = 'history' AND G.lastmodified IS NOT NULL
>AND G.dateinitiated IS NULL
>WHERE A.firmid = MMColParam AND a.archived LIKE 'MMColParam3'
>ORDER BY MMColParam2
>
>--
>Any help is appreciated.
>Aleks
>
We see the world, not as it is, but as we are.