DISTINCT query issue

I'm familiar with MySQL and recently started working with MSSQL.
I have a general understanding of how the DISTINCT command works, but would like to expand on that a bit.

I need to do a DISTINCT query on a record's 'name' column, but i need all of the values for each of those returned records. So, I need the query to only apply the DISTINCT command to the 'name' column, not the other columns of the received records.

Can someone explain a bit how to word this

Many thanks in advance.

EDIT: Upon reflection, I realize now that this request is counter to the way DISTINCT works. D'oh. Move along, nothing to see here. :(

Hi,

As you have wrote at the end of your message, you can not get such a list.

But if you want to get records of having same specific column value, you can run a select and order by over that column also can add a criteria to filter returned data.

Distinct in Select Statement

Hey there, is there a way I can use command such as distinct in a select statement to do the following. Lets say I want to do a search of products based off their location and I want to list the companies that will have products in that area. I only want to list the company once, but if I'm searching by products in the area I might come up with 15 results for that company. I have not written the code yet for this, I'm just planning ahead.

I'm programming using VB so I guess I would do something like this.

State = Trim(Request.QueryString("State"))

SelectStatement = "Select * From Products Where State='" & _
State & "'"

This would of course give me hypothetically speaking a list as long as the amount of products in one given area. Is there a way to cut this down and only list the company once? Any help would be greatly appreciated. Thanks in advance.SELECT DISTINCT(CompanyName)
FROM Company
WHERE Product.State = stateVariable
AND Product.CompanyID = Company.CompanyID|||thanks, I'll give that a shot.

Distinct Command ...

mytable

Cat1

Cat2

Cat3

Start price

End price

A

a

a

12

21

A

a

b

98

11

A

a

a

99

12

A

b

b

12

11

A

b

a

9

0

A

b

b

1

1

…

…

…

…

…

…

…

…

…

…

My result should be like this …..

( Result of SQL Query? )

Cat1

Cat2

Cat3

A

a

a

A

a

b

A

b

a

A

b

b

…

…

…

…

…

…

What is the sql command to do like this?

Maybe this will work for you?

create

table #test( cat1varchar(10),

cat2

varchar(10),

cat3

varchar(10),

StartPrice

int,

EndPrice

int)

insert

into #test

values

('A','a','a', 12,21)

insert

into #test

values

('A','a','b', 98,11)

insert

into #test

values

('A','a','a', 99,12)

insert

into #test

values

('A','b','b', 12,11)

insert

into #test

values

('A','b','a', 9,0)

select

*from #test

Select

Distinct cat1,cat2,cat3from #test

drop

table #test|||

You already have the answer... DISTINCT

Try:

SELECT DISTINCT Cat1, Cat2, Cat3 FROM mytable

|||

cloris:

You already have the answer... DISTINCT

Try:

SELECT DISTINCT Cat1, Cat2, Cat3 FROM mytable

Haha ha,, i am foolish ........

Thanks cloris

|||It's one of those things. The languages typically have an easy way of doing something if you know where to look. Good Luck.

Displaying error when using insert command

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:intranetnewConnectionString %>"

InsertCommand="INSERT INTO timeoffcalc(Typeoftime, amountoftime, employeeID) VALUES (@.TypeofTime, @.Amountoftime, @.emplID)"

I have the emplyID and Typeof time as a PK. When the user enters a duplicate value it just gives them the error page. Can i set a label or something to notify the user of the error instead of the error page?

UseTry- Catch block .. in the catch block put customized error message.

Hope it helps..

|||Problem is, I dont know how to do that when using a command object. All i have is the insertcommand function. Where would i put it?|||

try

whatever.insert

catch ex as exception

lblError.text=ex.message

end try

|||

Boy I feel dumb. I am using details view with the insert. I dont see where the command is fired in the code. I click insert in the details view and it does it. Where would i put that code. I know how to do a try catch, but i am confused to where the code is fired with the click, here is my code.

<asp:SqlDataSource

ID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:intranetnewConnectionString %>"

SelectCommand="SELECT emplID, lname + ' , ' + fname AS Name FROM employeemain ORDER BY lname">

</asp:SqlDataSource>

<asp:DetailsViewID="DetailsView1"runat="server"AutoGenerateRows="False"BackColor="White"

BorderColor="#DEDFDE"BorderStyle="None"BorderWidth="1px"CellPadding="4"DataSourceID="SqlDataSource2"

DefaultMode="Insert"ForeColor="Black"GridLines="Horizontal"Height="50px"Width="125px"DataKeyNames="emplID">

<FooterStyleBackColor="#CCCC99"/>

<EditRowStyleBackColor="#CE5D5A"Font-Bold="True"ForeColor="White"/>

<RowStyleBackColor="#F7F7DE"/>

<PagerStyleBackColor="#F7F7DE"ForeColor="Black"HorizontalAlign="Right"/>

<Fields>

<asp:TemplateFieldHeaderText="Employee's Name"SortExpression="emplID">

<EditItemTemplate>

<asp:TextBoxID="TextBox2"runat="server"Text='<%# Bind("emplID") %>'></asp:TextBox>

</EditItemTemplate>

<InsertItemTemplate>

<asp:DropDownListID="DropDownList2"runat="server"DataSourceID="SqlDataSource1"

DataTextField="Name"DataValueField="emplID"SelectedValue='<%# Bind("emplID") %>'>

</asp:DropDownList>

</InsertItemTemplate>

<ItemTemplate>

<asp:LabelID="Label2"runat="server"Text='<%# Bind("emplID") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateFieldHeaderText="Type of Time"SortExpression="Typeoftime">

<EditItemTemplate>

<asp:TextBoxID="TextBox1"runat="server"Text='<%# Bind("Typeoftime") %>'></asp:TextBox>

</EditItemTemplate>

<InsertItemTemplate>

<asp:DropDownListID="DropDownList1"runat="server"SelectedValue='<%# Bind("Typeoftime") %>'>

<asp:ListItem>Vacation</asp:ListItem>

<asp:ListItem>Personal</asp:ListItem>

<asp:ListItem>Sick</asp:ListItem>

<asp:ListItem></asp:ListItem>

</asp:DropDownList>

</InsertItemTemplate>

<ItemTemplate>

<asp:LabelID="Label1"runat="server"Text='<%# Bind("Typeoftime") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:BoundFieldDataField="amountoftime"HeaderText="Amount per Month"SortExpression="amountoftime"/>

<asp:CommandFieldShowInsertButton="True"/>

</Fields>

<HeaderStyleBackColor="#6B696B"Font-Bold="True"ForeColor="White"/>

<AlternatingRowStyleBackColor="White"/>

</asp:DetailsView>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:intranetnewConnectionString %>"

InsertCommand="INSERT INTO timeoffcalc(Typeoftime, amountoftime, employeeID) VALUES (@.TypeofTime, @.Amountoftime, @.emplID)"

SelectCommand="SELECT timeoffcalc.Typeoftime, timeoffcalc.amountoftime, employeemain.emplID FROM employeemain INNER JOIN timeoffcalc ON employeemain.emplID = timeoffcalc.employeeID">

<InsertParameters>

<asp:ParameterName="TypeofTime"/>

<asp:ParameterName="Amountoftime"/>

<asp:ParameterName="emplID"/>

</InsertParameters>

</asp:SqlDataSource>

<asp:LabelID="Label3"runat="server"Font-Italic="True"Font-Size="Large"ForeColor="Red"

Text="It has been Added"Visible="False"></asp:Label><br/>

<asp:ButtonID="Button1"runat="server"Text="Add Another"Visible="False"/><br/>

</asp:Content>

|||

Ah...

Well try catching the sqldatasource2_inserted event, I believe you can catch the errors there, and then set the e parameter to something to say you handled the error, but I've never actually done it.

displaying database options

What tsql command can I use to display what database options are in
affect? Such as ansi nulls, ansi warnings, etc. sp_dboption gives me
a list, but doesn't tell me their values.
Thanks,
waltmallon@.yahoo.com<waltmallon@.yahoo.com> wrote in message
news:1115834655.031449.34870@.g44g2000cwa.googlegroups.com...
> What tsql command can I use to display what database options are in
> affect? Such as ansi nulls, ansi warnings, etc. sp_dboption gives me
> a list, but doesn't tell me their values.
did u try:
exec sp_dboption 'db_name'|||That works. Thanks. Are there any other commands, such as DBCC
commands, that display this information?
Thanks alot!
Walter|||dbcc useroptions
this is cnnection-specific so can differ between clients and has no direct
relationship to options set at the database level if they have changed by
this connection prior to running the statement...
<waltmallon@.yahoo.com> wrote in message
news:1115835691.445076.292710@.g43g2000cwa.googlegroups.com...
> That works. Thanks. Are there any other commands, such as DBCC
> commands, that display this information?
> Thanks alot!
> Walter
>

displaying database options

What tsql command can I use to display what database options are in
affect? Such as ansi nulls, ansi warnings, etc. sp_dboption gives me
a list, but doesn't tell me their values.
Thanks,
waltmallon@.yahoo.com
<waltmallon@.yahoo.com> wrote in message
news:1115834655.031449.34870@.g44g2000cwa.googlegro ups.com...
> What tsql command can I use to display what database options are in
> affect? Such as ansi nulls, ansi warnings, etc. sp_dboption gives me
> a list, but doesn't tell me their values.
did u try:
exec sp_dboption 'db_name'
|||That works. Thanks. Are there any other commands, such as DBCC
commands, that display this information?
Thanks alot!
Walter
|||dbcc useroptions
this is cnnection-specific so can differ between clients and has no direct
relationship to options set at the database level if they have changed by
this connection prior to running the statement...
<waltmallon@.yahoo.com> wrote in message
news:1115835691.445076.292710@.g43g2000cwa.googlegr oups.com...
> That works. Thanks. Are there any other commands, such as DBCC
> commands, that display this information?
> Thanks alot!
> Walter
>

displaying database options

What tsql command can I use to display what database options are in
affect? Such as ansi nulls, ansi warnings, etc. sp_dboption gives me
a list, but doesn't tell me their values.
Thanks,
waltmallon@.yahoo.com<waltmallon@.yahoo.com> wrote in message
news:1115834655.031449.34870@.g44g2000cwa.googlegroups.com...
> What tsql command can I use to display what database options are in
> affect? Such as ansi nulls, ansi warnings, etc. sp_dboption gives me
> a list, but doesn't tell me their values.
did u try:
exec sp_dboption 'db_name'|||That works. Thanks. Are there any other commands, such as DBCC
commands, that display this information?
Thanks alot!
Walter|||dbcc useroptions
this is cnnection-specific so can differ between clients and has no direct
relationship to options set at the database level if they have changed by
this connection prior to running the statement...
<waltmallon@.yahoo.com> wrote in message
news:1115835691.445076.292710@.g43g2000cwa.googlegroups.com...
> That works. Thanks. Are there any other commands, such as DBCC
> commands, that display this information?
> Thanks alot!
> Walter
>

Display Tabular Form from DBCC SHOWCONTIG Command

Dear all,
I want to display information resulted by DBCC SHOWCONTIG Command in
tabular form like below:
(Pages Scanned) (Extents Scanned) (Avg. Pages per Extent)..
TABLE A
TABLE B
TABLE C
...
Is it possible?
Thanks
Robert LieUse the WITH TABLERESULTS option. Actually, if you look in Books Online (upd
ated version), you have
a sample for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23lOMj05VFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> I want to display information resulted by DBCC SHOWCONTIG Command in tabul
ar form like below:
> (Pages Scanned) (Extents Scanned) (Avg. Pages per Extent)..
> TABLE A
> TABLE B
> TABLE C
> ...
> Is it possible?
> Thanks
> Robert Lie

Display Tabular Form from DBCC SHOWCONTIG Command

Dear all,
I want to display information resulted by DBCC SHOWCONTIG Command in
tabular form like below:
(Pages Scanned) (Extents Scanned) (Avg. Pages per Extent)..
TABLE A
TABLE B
TABLE C
...
Is it possible?
Thanks
Robert Lie
Use the WITH TABLERESULTS option. Actually, if you look in Books Online (updated version), you have
a sample for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23lOMj05VFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> I want to display information resulted by DBCC SHOWCONTIG Command in tabular form like below:
> (Pages Scanned) (Extents Scanned) (Avg. Pages per Extent)..
> TABLE A
> TABLE B
> TABLE C
> ...
> Is it possible?
> Thanks
> Robert Lie

Display Tabular Form from DBCC SHOWCONTIG Command

Dear all,
I want to display information resulted by DBCC SHOWCONTIG Command in
tabular form like below:
(Pages Scanned) (Extents Scanned) (Avg. Pages per Extent)..
TABLE A
TABLE B
TABLE C
...
Is it possible?
Thanks
Robert LieUse the WITH TABLERESULTS option. Actually, if you look in Books Online (updated version), you have
a sample for this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23lOMj05VFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> I want to display information resulted by DBCC SHOWCONTIG Command in tabular form like below:
> (Pages Scanned) (Extents Scanned) (Avg. Pages per Extent)..
> TABLE A
> TABLE B
> TABLE C
> ...
> Is it possible?
> Thanks
> Robert Lie

Display Tables/Design Query?

Is there a SQL command that will display the tables in a SQL 2000 database o
r
describe the layout of a table in the DB?BW,
Try:
http://www.aspfaq.com/show.asp?id=2178
and
http://www.aspfaq.com/show.asp?id=2177
and/or
SP_HELP <TABLE>
SP_COLUMNS <TABLE>
and/or
Enterprise Manager - Database Diagrams (graphical visualization)
and/or
Visio
and/or
third-party tool i.e., ERwin
etc...
HTH
Jerry
"-BW" <-BW@.discussions.microsoft.com> wrote in message
news:D126A965-39B1-48B7-8FF5-49DCE9F9182F@.microsoft.com...
> Is there a SQL command that will display the tables in a SQL 2000 database
> or
> describe the layout of a table in the DB?|||Create a diagram and click add related tables when choosing your tables
You will see all the tables with their relationships
http://sqlservercode.blogspot.com/
"-BW" wrote:

> Is there a SQL command that will display the tables in a SQL 2000 database
or
> describe the layout of a table in the DB?

display sql xml result

hi all...
i'm using the command below to get a recodset from a sql2000 in a xml
structure.
select * from tb_perguntas FOR XML RAW
i know that with this command i can get a result like this:.
<Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders OrderID="10643" OrderDate="08/25/1997" />
<Orders OrderID="11011" OrderDate="04/09/1998" />
</Customers>
i'm having problems getting this xml string from a ASP page.
i tried:
<%
Dim connObj
Set connObj = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
connObj.ConnectionString = "DRIVER={SQL
Server};SERVER=y;UID=x;PWD=z;DATABASE=h"
connObj.Open
Set rs = connObj.Execute("select * from tb_perguntas FOR XML RAW")
response.write( "rs:" & rs.fields(0).value)
%>
the only thing i want it's display the complete string SQL returned.
please help.
thanks.
please help.
You need to use the ICommandStream interface and not the recordset to get
your XML back. See the documentation for examples.
Also, FOR XML raw gives you no hierarchy in the XML, use the AUTO mode or
EXPLICIT mode instead in 2000 (and the PATH mode in 2005).
Best regards
Michael
"rodrigo" <emaildorodrigo@.yahoo.com> wrote in message
news:%23XeLzaYWGHA.3328@.TK2MSFTNGP02.phx.gbl...
> hi all...
> i'm using the command below to get a recodset from a sql2000 in a xml
> structure.
> select * from tb_perguntas FOR XML RAW
> i know that with this command i can get a result like this:.
> <Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
> <Orders OrderID="10643" OrderDate="08/25/1997" />
> <Orders OrderID="11011" OrderDate="04/09/1998" />
> </Customers>
> i'm having problems getting this xml string from a ASP page.
> i tried:
> <%
> Dim connObj
> Set connObj = Server.CreateObject("ADODB.Connection")
> Set rs = Server.CreateObject("ADODB.Recordset")
> connObj.ConnectionString = "DRIVER={SQL
> Server};SERVER=y;UID=x;PWD=z;DATABASE=h"
> connObj.Open
> Set rs = connObj.Execute("select * from tb_perguntas FOR XML RAW")
> response.write( "rs:" & rs.fields(0).value)
> %>
> the only thing i want it's display the complete string SQL returned.
> please help.
> thanks.
> please help.
>

display sql xml result

hi all...
i'm using the command below to get a recodset from a sql2000 in a xml
structure.
select * from tb_perguntas FOR XML RAW
i know that with this command i can get a result like this:.
<Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders OrderID="10643" OrderDate="08/25/1997" />
<Orders OrderID="11011" OrderDate="04/09/1998" />
</Customers>
i'm having problems getting this xml string from a ASP page.
i tried:
<%
Dim connObj
Set connObj = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
connObj.ConnectionString = "DRIVER={SQL
Server};SERVER=y;UID=x;PWD=z;DATABASE=h"
connObj.Open
Set rs = connObj.Execute("select * from tb_perguntas FOR XML RAW")
response.write( "rs:" & rs.fields(0).value)
%>
the only thing i want it's display the complete string SQL returned.
please help.
thanks.
please help.You need to use the ICommandStream interface and not the recordset to get
your XML back. See the documentation for examples.
Also, FOR XML raw gives you no hierarchy in the XML, use the AUTO mode or
EXPLICIT mode instead in 2000 (and the PATH mode in 2005).
Best regards
Michael
"rodrigo" <emaildorodrigo@.yahoo.com> wrote in message
news:%23XeLzaYWGHA.3328@.TK2MSFTNGP02.phx.gbl...
> hi all...
> i'm using the command below to get a recodset from a sql2000 in a xml
> structure.
> select * from tb_perguntas FOR XML RAW
> i know that with this command i can get a result like this:.
> <Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
> <Orders OrderID="10643" OrderDate="08/25/1997" />
> <Orders OrderID="11011" OrderDate="04/09/1998" />
> </Customers>
> i'm having problems getting this xml string from a ASP page.
> i tried:
> <%
> Dim connObj
> Set connObj = Server.CreateObject("ADODB.Connection")
> Set rs = Server.CreateObject("ADODB.Recordset")
> connObj.ConnectionString = "DRIVER={SQL
> Server};SERVER=y;UID=x;PWD=z;DATABASE=h"
> connObj.Open
> Set rs = connObj.Execute("select * from tb_perguntas FOR XML RAW")
> response.write( "rs:" & rs.fields(0).value)
> %>
> the only thing i want it's display the complete string SQL returned.
> please help.
> thanks.
> please help.
>|||hi michael, thanks for the reply.
i looked here:
http://support.microsoft.com/kb/272185/EN-US/
[url]http://www.com.it-berater.org/data_access/oledb/interfaces/ICommandStream.htm[/url
]
and there is no example on how to implement this xml return in asp. the
sample is for win32 console applications.
i can return the normal (not xml) search and then manually get the fields
and values form the recordset and set up the nodes adding the "<" "/>" tags,
however if i can already ge the return in xml why bother creating the tags.
i just can't addimit that nobody have done this before..or maybe it's not
htat simple as i think...
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> escreveu na mensagem
news:ew3DUPcWGHA.3492@.TK2MSFTNGP05.phx.gbl...
> You need to use the ICommandStream interface and not the recordset to get
> your XML back. See the documentation for examples.
> Also, FOR XML raw gives you no hierarchy in the XML, use the AUTO mode or
> EXPLICIT mode instead in 2000 (and the PATH mode in 2005).
> Best regards
> Michael
> "rodrigo" <emaildorodrigo@.yahoo.com> wrote in message
> news:%23XeLzaYWGHA.3328@.TK2MSFTNGP02.phx.gbl...
>|||Did you take a look at
http://msdn.microsoft.com/library/d...>
DOandXML.asp
?
Best regards
Michael
PS: You may need to adjust the provider string...
"rodrigo" <emaildorodrigo@.yahoo.com> wrote in message
news:%23SedSHpWGHA.5012@.TK2MSFTNGP05.phx.gbl...
> hi michael, thanks for the reply.
> i looked here:
> http://support.microsoft.com/kb/272185/EN-US/
> [url]http://www.com.it-berater.org/data_access/oledb/interfaces/ICommandStream.htm[/u
rl]
> and there is no example on how to implement this xml return in asp. the
> sample is for win32 console applications.
> i can return the normal (not xml) search and then manually get the fields
> and values form the recordset and set up the nodes adding the "<" "/>"
> tags,
> however if i can already ge the return in xml why bother creating the
> tags.
> i just can't addimit that nobody have done this before..or maybe it's not
> htat simple as i think...
>
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> escreveu na mensagem
> news:ew3DUPcWGHA.3492@.TK2MSFTNGP05.phx.gbl...
>