SELECT DISTINCT Query not working

Chris · Oct 24, 2004

Please could someone take a look at following code .I want it to select
Distinct options but keep on getting multiple of the same selection.
eg:

Isat
Isat
Isat
Kemco

The choices are run from a database selection in a dropdown box.
Please could some one help.
Thanks Chris

Code follows
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
""""""""""""""""""""""""""""""""""""""""""
<html>

<head>
<% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do not
Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Sub Search</title>
</head>

<body bgcolor="#C0C0C0">


<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database
Results component on this page is unable to display database content. The
page must have a filename ending in '.asp', and the web must be hosted on a
server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT DISTINCT * FROM Results WHERE (Maincategory =
'::Maincategory::') ORDER BY Sublist ASC"
fp_sDefault="Maincategory="
fp_sNoRecords="No records returned."
fp_sDataConn="Sublisting"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&ID=3&Maincategory=202&Sublist=202&Remote_computer_name=202&Us
er_name=202&Browser_type=202&Timestamp=135&"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

<ul>
<li>
<a target="_self"
href="Areaselect.asp?Sublist=<%=FP_FieldURL(fp_rs,"Sublist")%>">
<%=FP_FieldVal(fp_rs,"Sublist")%></a></li>
</ul>



</body>

</html>
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
""""""""""""""""""""""""""""""""""

TommyL · Oct 24, 2004

Hi Chris,

You Have
"SELECT DISTINCT * FROM Results WHERE.....
Change to
"SELECT DISTINCT FROM Results WHERE.....

See if that helps.. (dropping the * )

TL
| Please could someone take a look at following code .I want it to select
| Distinct options but keep on getting multiple of the same selection.
| eg:
|
| Isat
| Isat
| Isat
| Kemco
|
| The choices are run from a database selection in a dropdown box.
| Please could some one help.
| Thanks Chris
|
| Code follows
|
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
|
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
| """"""""""""""""""""""""""""""""""""""""""
| <html>
|
| <head>
| <% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do
not
| Edit.
| FP_CharSet = "windows-1252"
| FP_CodePage = 1252 %>
| <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
| <title>Sub Search</title>
| </head>
|
| <body bgcolor="#C0C0C0">
|
| 
| <% if 0 then %>
| <SCRIPT Language="JavaScript">
| document.write("<div style='background: yellow; color: black;'>The
Database
| Results component on this page is unable to display database content. The
| page must have a filename ending in '.asp', and the web must be hosted on
a
| server that supports Active Server Pages.</div>");
| </SCRIPT>
| <% end if %>
| <%
| fp_sQry="SELECT DISTINCT * FROM Results WHERE (Maincategory =
| '::Maincategory::') ORDER BY Sublist ASC"
| fp_sDefault="Maincategory="
| fp_sNoRecords="No records returned."
| fp_sDataConn="Sublisting"
| fp_iMaxRecords=256
| fp_iCommandType=1
| fp_iPageSize=0
| fp_fTableFormat=False
| fp_fMenuFormat=False
| fp_sMenuChoice=""
| fp_sMenuValue=""
|
fp_sColTypes="&ID=3&Maincategory=202&Sublist=202&Remote_computer_name=202&Us
| er_name=202&Browser_type=202&Timestamp=135&"
| fp_iDisplayCols=1
| fp_fCustomQuery=False
| BOTID=0
| fp_iRegion=BOTID
| %>
| 
| <ul>
| <li>
| <a target="_self"
| href="Areaselect.asp?Sublist=<%=FP_FieldURL(fp_rs,"Sublist")%>">
| <%=FP_FieldVal(fp_rs,"Sublist")%></a></li>
| </ul>
| 
| 
|
| </body>
|
| </html>
|
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
|
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
| """"""""""""""""""""""""""""""""""
|
|
|

TommyL · Oct 24, 2004

Chris,

Try this...

"SELECT DISTINCT Results FROM Maincategory"

"Results" being the table.. "Maincategory" the column

If this works then add the ordering (ORDER BY Sublist ASC)

TL

| Please could someone take a look at following code .I want it to select
| Distinct options but keep on getting multiple of the same selection.
| eg:
|
| Isat
| Isat
| Isat
| Kemco
|
| The choices are run from a database selection in a dropdown box.
| Please could some one help.
| Thanks Chris
|
| Code follows
|
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
|
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
| """"""""""""""""""""""""""""""""""""""""""
| <html>
|
| <head>
| <% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do
not
| Edit.
| FP_CharSet = "windows-1252"
| FP_CodePage = 1252 %>
| <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
| <title>Sub Search</title>
| </head>
|
| <body bgcolor="#C0C0C0">
|
| 
| <% if 0 then %>
| <SCRIPT Language="JavaScript">
| document.write("<div style='background: yellow; color: black;'>The
Database
| Results component on this page is unable to display database content. The
| page must have a filename ending in '.asp', and the web must be hosted on
a
| server that supports Active Server Pages.</div>");
| </SCRIPT>
| <% end if %>
| <%
| fp_sQry="SELECT DISTINCT * FROM Results WHERE (Maincategory =
| '::Maincategory::') ORDER BY Sublist ASC"
| fp_sDefault="Maincategory="
| fp_sNoRecords="No records returned."
| fp_sDataConn="Sublisting"
| fp_iMaxRecords=256
| fp_iCommandType=1
| fp_iPageSize=0
| fp_fTableFormat=False
| fp_fMenuFormat=False
| fp_sMenuChoice=""
| fp_sMenuValue=""
|
fp_sColTypes="&ID=3&Maincategory=202&Sublist=202&Remote_computer_name=202&Us
| er_name=202&Browser_type=202&Timestamp=135&"
| fp_iDisplayCols=1
| fp_fCustomQuery=False
| BOTID=0
| fp_iRegion=BOTID
| %>
| 
| <ul>
| <li>
| <a target="_self"
| href="Areaselect.asp?Sublist=<%=FP_FieldURL(fp_rs,"Sublist")%>">
| <%=FP_FieldVal(fp_rs,"Sublist")%></a></li>
| </ul>
| 
| 
|
| </body>
|
| </html>
|
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
|
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
| """"""""""""""""""""""""""""""""""
|
|
|

TommyL · Oct 24, 2004

OK Wake up... Sorry

"SELECT DISTINCT Maincategory FROM Results"

"Results" being the table.. "Maincategory" the column

That should work....

TL
| Please could someone take a look at following code .I want it to select
| Distinct options but keep on getting multiple of the same selection.
| eg:
|
| Isat
| Isat
| Isat
| Kemco
|
| The choices are run from a database selection in a dropdown box.
| Please could some one help.
| Thanks Chris
|
| Code follows
|
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
|
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
| """"""""""""""""""""""""""""""""""""""""""
| <html>
|
| <head>
| <% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do
not
| Edit.
| FP_CharSet = "windows-1252"
| FP_CodePage = 1252 %>
| <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
| <title>Sub Search</title>
| </head>
|
| <body bgcolor="#C0C0C0">
|
| 
| <% if 0 then %>
| <SCRIPT Language="JavaScript">
| document.write("<div style='background: yellow; color: black;'>The
Database
| Results component on this page is unable to display database content. The
| page must have a filename ending in '.asp', and the web must be hosted on
a
| server that supports Active Server Pages.</div>");
| </SCRIPT>
| <% end if %>
| <%
| fp_sQry="SELECT DISTINCT * FROM Results WHERE (Maincategory =
| '::Maincategory::') ORDER BY Sublist ASC"
| fp_sDefault="Maincategory="
| fp_sNoRecords="No records returned."
| fp_sDataConn="Sublisting"
| fp_iMaxRecords=256
| fp_iCommandType=1
| fp_iPageSize=0
| fp_fTableFormat=False
| fp_fMenuFormat=False
| fp_sMenuChoice=""
| fp_sMenuValue=""
|
fp_sColTypes="&ID=3&Maincategory=202&Sublist=202&Remote_computer_name=202&Us
| er_name=202&Browser_type=202&Timestamp=135&"
| fp_iDisplayCols=1
| fp_fCustomQuery=False
| BOTID=0
| fp_iRegion=BOTID
| %>
| 
| <ul>
| <li>
| <a target="_self"
| href="Areaselect.asp?Sublist=<%=FP_FieldURL(fp_rs,"Sublist")%>">
| <%=FP_FieldVal(fp_rs,"Sublist")%></a></li>
| </ul>
| 
| 
|
| </body>
|
| </html>
|
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
|
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
| """"""""""""""""""""""""""""""""""
|
|
|

Kevin Spencer · Oct 25, 2004

"SELECT DISTINCT Maincategory FROM Results"

This is the correct answer. For future reference, let me tell you why:

The DISTINCT Operator in SQL ensures that records that are identical are
combined into single records. Note that I said "records" not "columns." This
means that if you select more than one column in a record set, ALL columns
selected must be the same in order for 2 records to be combined. When you
use "SELECT *..." you are fetching ALL columns from the table, not just the
"Maincategory" column, and if, for example, that table has a primary key
field, such as an AutoNumber field, that field will not have the same value
in both records, and they will not be combined.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

TommyL said:
OK Wake up... Sorry

"SELECT DISTINCT Maincategory FROM Results"

"Results" being the table.. "Maincategory" the column

That should work....

TL
| Please could someone take a look at following code .I want it to select
| Distinct options but keep on getting multiple of the same selection.
| eg:
|
| Isat
| Isat
| Isat
| Kemco
|
| The choices are run from a database selection in a dropdown box.
| Please could some one help.
| Thanks Chris
|
| Code follows
|
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

|

Click to expand...

""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
| """"""""""""""""""""""""""""""""""""""""""
| <html>
|
| <head>
| <% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do
not
| Edit.
| FP_CharSet = "windows-1252"
| FP_CodePage = 1252 %>
| <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
| <title>Sub Search</title>
| </head>
|
| <body bgcolor="#C0C0C0">
|
| 
| <% if 0 then %>
| <SCRIPT Language="JavaScript">
| document.write("<div style='background: yellow; color: black;'>The
Database
| Results component on this page is unable to display database content. The
| page must have a filename ending in '.asp', and the web must be hosted on
a
| server that supports Active Server Pages.</div>");
| </SCRIPT>
| <% end if %>
| <%
| fp_sQry="SELECT DISTINCT * FROM Results WHERE (Maincategory =
| '::Maincategory::') ORDER BY Sublist ASC"
| fp_sDefault="Maincategory="
| fp_sNoRecords="No records returned."
| fp_sDataConn="Sublisting"
| fp_iMaxRecords=256
| fp_iCommandType=1
| fp_iPageSize=0
| fp_fTableFormat=False
| fp_fMenuFormat=False
| fp_sMenuChoice=""
| fp_sMenuValue=""
|
fp_sColTypes="&ID=3&Maincategory=202&Sublist=202&Remote_computer_name=202&Us
| er_name=202&Browser_type=202&Timestamp=135&"
| fp_iDisplayCols=1
| fp_fCustomQuery=False
| BOTID=0
| fp_iRegion=BOTID
| %>
| 
| <ul>
| <li>
| <a target="_self"
| href="Areaselect.asp?Sublist=<%=FP_FieldURL(fp_rs,"Sublist")%>">
| <%=FP_FieldVal(fp_rs,"Sublist")%></a></li>
| </ul>
| 
| 
|
| </body>
|
| </html>
|
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

|

Click to expand...

""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
| """"""""""""""""""""""""""""""""""
|
|
|

I am trying to understand this code...	3	Jul 26, 2008
any ideas why I can update one table in my database but not the other?	2	Sep 22, 2006
@ my wits end	1	Sep 12, 2006
Please help, FP generated code produces a "Too few parameters. Expected1" error!	7	Jun 6, 2005
All values item in a dropdown box for numeric field query	0	Aug 24, 2006
Select statement aanpassen in asp gedeelte middels variable	1	Mar 30, 2007
Database Results Error	1	Sep 9, 2004
DataBase results Error continued	1	Sep 10, 2004

SELECT DISTINCT Query not working

Chris

TommyL

TommyL

TommyL

Kevin Spencer

Ask a Question

Similar Threads