How to use the TOP(N) Filter syntax?

Im tearing my hair out on this one.
Could anyone provide information on how to use the "TOP(N)" filter syntax? I
want to return the top 10 aggregate rows. Not sure if it can do this?
Please help!
Thanks
TazYou mean like this:
SELECT TOP 500 Client.CLI_LastName
from Client
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:%235obeed0GHA.1268@.TK2MSFTNGP02.phx.gbl...
> Im tearing my hair out on this one.
> Could anyone provide information on how to use the "TOP(N)" filter syntax?
> I want to return the top 10 aggregate rows. Not sure if it can do this?
> Please help!
> Thanks
> Taz
>|||Just try something like this in your dataset
SELECT TOP (10) SomeField, COUNT(*) AS count
FROM SomeTable
GROUP BY SomeField
ORDER BY count DESC
That will count the number or rows pertaining to that specific field and
then put them in order by the count, showing only the top 10
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:%235obeed0GHA.1268@.TK2MSFTNGP02.phx.gbl...
> Im tearing my hair out on this one.
> Could anyone provide information on how to use the "TOP(N)" filter syntax?
> I want to return the top 10 aggregate rows. Not sure if it can do this?
> Please help!
> Thanks
> Taz
>|||Hi guys, thanks for the responses.
I cannot change my query, therefore the filtering has to happen after the
dataset has been returned. This is because below my chart I will sumamrise
ALL of the informarion, however I only want the chart to display the top 10
entires (or whatever).
I thought i could do something like..
Count(Field!MyAggregate.Value) Top N 10
However this didnt do anything (that I could see).
Kind Regards
Taz|||another option is two have 2 datasets, make them the exact same except add
the top N statement to the dataset related to the chart and then have your
second dataset without the top N for the summary information. You would
still get the same info but from 2 different datasets.
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:%23xy8bre0GHA.4648@.TK2MSFTNGP04.phx.gbl...
> Hi guys, thanks for the responses.
> I cannot change my query, therefore the filtering has to happen after the
> dataset has been returned. This is because below my chart I will
> sumamrise ALL of the informarion, however I only want the chart to display
> the top 10 entires (or whatever).
> I thought i could do something like..
> Count(Field!MyAggregate.Value) Top N 10
> However this didnt do anything (that I could see).
> Kind Regards
> Taz
>|||Many thanks for your help Ben,
your previous post was the one I needed to make me realise where I was going
wrong.
Thanks again,
Taz
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:OG$qsue0GHA.4044@.TK2MSFTNGP04.phx.gbl...
> another option is two have 2 datasets, make them the exact same except add
> the top N statement to the dataset related to the chart and then have your
> second dataset without the top N for the summary information. You would
> still get the same info but from 2 different datasets.
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:%23xy8bre0GHA.4648@.TK2MSFTNGP04.phx.gbl...
>> Hi guys, thanks for the responses.
>> I cannot change my query, therefore the filtering has to happen after the
>> dataset has been returned. This is because below my chart I will
>> sumamrise ALL of the informarion, however I only want the chart to
>> display the top 10 entires (or whatever).
>> I thought i could do something like..
>> Count(Field!MyAggregate.Value) Top N 10
>> However this didnt do anything (that I could see).
>> Kind Regards
>> Taz
>

How to use the return of select .... for xml auto...?

There is a lot of limitation on for xml clause. Is it possible to combine
several select statement with for xml to a big xml file?
Basically I want to implement something like:
select ... for xml auto
union all
select ... for xml auto
union all
.....(select ...
union all
select ...)for xml auto
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/

How to use SELECT UPPER

Hi

I want to return distinct values from a table in uppercase

Have tried

SELECT UPPER DISTINCT fieldname FROM tablename

But returns an error, what is the correct syntax.

ThanksNearly, upper is a function so you need to provide a column as an argument.
Select distinct upper(fieldname) from tablename|||Thanks,

However that now causes an error, my select statement is now:

SELECT DISTINCT UPPER (maketext) from VsVehicles WHERE dealerrefnum=8479

This is used in a datareader i.e.

Dim MySQL As String = "SELECT DISTINCT UPPER (maketext) from VsVehicles WHERE dealerrefnum=8479"
Dim MyConnection As New SqlConnection(ConnectionString)
Dim DataReader As SqlDataReader
Dim SQLCommand As New SqlCommand(MySQL, MyConnection)
MyConnection.Open()
DataReader = SQLCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
DropDownList1.DataSource = DataReader
DropDownList1.DataTextField = ("maketext")
DropDownList1.DataBind()

The error message is:

DataBinder.Eval: 'System.Data.Common.DbDataRecord' does not contain a property with the name maketext.

Thanks

Ben|||it doesnt seem to be the problem with the upper word. check if the col name is correct..

hth|||When you run a column through a function, you need to provide the result with an alias:

SELECT DISTINCT UPPER (maketext) AS maketext from VsVehicles WHERE dealerrefnum=8479

SELECT SUM(Volume) AS Volume FROM MilkBottles|||nope an alias is not necessary...unless you want to parse through the loop ( if the query returns one ) or get the value into a variable...you dont need an alias when you use a function...alias is only a way to identify the column...

hth|||Well, I could be wrong, but he is databinding to a datareader, and setting the DataTextField to a column named "maketext."

His query:

SELECT DISTINCT UPPER (maketext) from VsVehicles WHERE dealerrefnum=8479

returns no columns named "maketext." It does include a column (with no column name) derived from the column named "maketext", but no actual column named "maketext".

My guess is that aliasing the column name will solve his problem...|||i see what you mean...i was looking at the select stmt all the while...

::nope an alias is not necessary...unless you want to parse through the loop ( if the query
::returns one ) or get the value into a variable...you dont need an alias when you use a
::function...alias is only a way to identify the column...

from my stmt, i meant the same thing by saying "get the value into a variable"

i was prbly not clear...good you clarified it out..

dinakar|||Yes that worked, thankyou

SELECT DISTINCT UPPER (maketext) AS maketext FROM VsVehicles

Thanks

Bensql

How To Use Return Value from SqlDataSource

I have the following code in my page:

<asp:SqlDataSourceID="SqlHolidayDateRange"runat="server"ConnectionString="(OurConnectionString)"

SelectCommand="CountHolidays"SelectCommandType="StoredProcedure">

<SelectParameters><asp:ControlParameterControlID="WeekEndingDatePicker"Name="EndDate"PropertyName="SelectedDate"/><asp:ParameterDirection="ReturnValue"Name="RowCount"Type="Int32"/></SelectParameters></asp:SqlDataSource>

I need to place RowCount, the returned integer value from the stored procedure CountHolidays into a field on the web page. How in the world do I access that data?

Everything I've been able to find works if I'm defining my own command object and writing this thing from scratch. What I - and you -have to work with is what's above.

Hi,

Add OnSelected="SqlHolidayDateRange_Selected" to SqlDataSource then you can get return value using

Convert.ToInt32(e.Command.Parameters["@.RowCount"].Value);

Hope this helps.

|||

Like this:

voidSqlHolidayDateRange_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
int RowCount = Convert.ToInt32(e.Command.Parameters["@.RowCount"].Value);

//add your code.......

}

|||I'm sorry, but I'mreally new at this. Neither of your answers make much sense to me. Where do I add those things? In the page, in the codebehind, or what?|||

Doesn't matter.

Add OnSelected="SqlHolidayDateRange_Selected" in your page:

<asp:SqlDataSourceID="SqlHolidayDateRange"runat="server"ConnectionString="(OurConnectionString)"

SelectCommand="CountHolidays"SelectCommandType="StoredProcedure">

OnSelected="SqlHolidayDateRange_Selected"

<SelectParameters>

<asp:ControlParameterControlID="WeekEndingDatePicker"Name="EndDate"PropertyName="SelectedDate"/>

<asp:ParameterDirection="ReturnValue"Name="RowCount"Type="Int32"/>

</SelectParameters>

</asp:SqlDataSource>

And add the following to you code-behinde:

void SqlHolidayDateRange_Selected(object sender, SqlDataSourceStatusEventArgs e) {int RowCount = Convert.ToInt32(e.Command.Parameters["@.RowCount"].Value);//add your code.......}

Then you can display RowCount .

You can take a look atSqlDataSource in QuickStart Tutorials.

How to use CASE WHEN statement in Function

Hi,
My Function as follow-->
CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
RETURNS CHAR(9)
AS
BEGIN
IF @.cemk = '1'
BEGIN
RETURN 'XX0400100'
END
RETURN 'XX0400200'
END
My Case When statement like this-->
CASE
WHEN iv_cemk = 0 THEN 'XX0400200'
ELSE 'XX0400100'
END AS iv_cemk
And could I replace IF @.cmk='1' BEGIN... to CASE WHEN statement in
Function?
How should I do?
Thanks!
AngiHi Angi,
what about
CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
RETURNS CHAR(9)
AS
BEGIN
declare @.cReturn char(9)
set @.cReturn = (case @.cemk
when '1' then 'XX0400100'
else 'XX0400200'
end)
return @.cReturn
END
HTH
Meinhard
"angi" <angi@.microsoft.public.sqlserver.olap> schrieb im Newsbeitrag
news:OJrAXO4pEHA.2864@.TK2MSFTNGP12.phx.gbl...
> Hi,
> My Function as follow-->
> CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
> RETURNS CHAR(9)
> AS
> BEGIN
> IF @.cemk = '1'
> BEGIN
> RETURN 'XX0400100'
> END
> RETURN 'XX0400200'
> END
> My Case When statement like this-->
> CASE
> WHEN iv_cemk = 0 THEN 'XX0400200'
> ELSE 'XX0400100'
> END AS iv_cemk
> And could I replace IF @.cmk='1' BEGIN... to CASE WHEN statement in
> Function?
> How should I do?
> Thanks!
> Angi
>|||Hi, Meinhard
Thank you very much!
And there is aother way like follow..
BEGIN
RETURN CASE WHEN @.cemk='1' THEN 'XX0400100'
ELSE 'XX0400200'
END
END
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
And I have another question there, syntax like follow..
CREATE FUNCTION fnXY13 (@.iden CHAR(4), @.csct CHAR(2), @.cect CHAR(2))
RETURNS CHAR(9)
AS
BEGIN
RETURN
CASE--XY13AA
WHEN @.iden = '7660' AND @.iden LIKE '00%%' THEN
CASE
WHEN @.csct = 'f1' THEN 'XY1300100'
WHEN @.csct = 'f2' THEN 'XY1300200'
ELSE 'XY1300500'
END
ELSE 'X99999999'
END
CASE--XY13BB
WHEN @.iden = '7660' AND @.iden LIKE '00%%' THEN
CASE
WHEN @.cect = 'f1' THEN 'XY1300100'
WHEN @.cect = 'f2' THEN 'XY1300200'
ELSE 'XY1300500'
END
ELSE 'X99999999'
END
CASE--XY13CC
WHEN @.iden IN ('0406','0408','0409','0419','0501') THEN
CASE
WHEN @.cect = 'f1' THEN 'XY1300100'
WHEN @.cect = 'f2' THEN 'XY1300200'
ELSE 'XY1300500'
END
ELSE 'X99999999'
END
END
There are 3 different CASE WHEN conditions and how could I combine it in a
Function?
Thanks!
Angi
"Meinhard Schnoor-Matriciani" <codehack@.freenet.de> ¼¶¼g©ó¶l¥ó·s»D
:2s4ev1F1h4vb5U1@.uni-berlin.de...
> Hi Angi,
> what about
>
> CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
> RETURNS CHAR(9)
> AS
> BEGIN
> declare @.cReturn char(9)
> set @.cReturn = (case @.cemk
> when '1' then 'XX0400100'
> else 'XX0400200'
> end)
> return @.cReturn
> END
> HTH
> Meinhard
> "angi" <angi@.microsoft.public.sqlserver.olap> schrieb im Newsbeitrag
> news:OJrAXO4pEHA.2864@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > My Function as follow-->
> >
> > CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
> > RETURNS CHAR(9)
> > AS
> > BEGIN
> > IF @.cemk = '1'
> > BEGIN
> > RETURN 'XX0400100'
> > END
> > RETURN 'XX0400200'
> > END
> >
> > My Case When statement like this-->
> >
> > CASE
> > WHEN iv_cemk = 0 THEN 'XX0400200'
> > ELSE 'XX0400100'
> > END AS iv_cemk
> >
> > And could I replace IF @.cmk='1' BEGIN... to CASE WHEN statement in
> > Function?
> > How should I do?
> >
> > Thanks!
> > Angi
> >
> >
>

how to use cairage return chr(13)

hi ,

i am unable to concatenate...
actually i want the id number to be in the same line

for ex: hi ur id no - 3456
i want to get in the above format..but i am getting it as

"hi ur id is-
3456"
pls help me out.. i should use this in the sqr report
so hoe should i use the carige return chr(13)

Quote:

Originally Posted by pkashyap

hi ,

i am unable to concatenate...
actually i want the id number to be in the same line

for ex: hi ur id no - 3456
i want to get in the above format..but i am getting it as

"hi ur id is-
3456"
pls help me out.. i should use this in the sqr report
so hoe should i use the carige return chr(13)

Hi. Would you please post the code that is causing this output. That would make things much easier.
Thanks

How to use a parameter to return all records

I am using the example from the Microsoft Official Course 2030A.
I want the option for a user to select valeu from a drop down to return all records or to choose individual ones from the multi-value check box.

My query is taking forever and as you see I just want the top ten

Select TOP 10 * from PRH_EOB

WHERE (MemberId = @.MemberId OR @.MemberId = 'ALL')

and (disenr_st = @.Disenroll or @.Disenroll = 'ALL')

and (LOB in (Select * from SplitList(',',@.LOB) as ListItem) or @.LOB = 'ALL')

and (Groupid in (Select * from SplitList(',',@.GroupList) as ListItem) or @.Grouplist = 'ALL')

and paydate between @.From and @.TO

order by Slastname, sFirstname, claimid, linenum

Thanks,
Phil

Come to find out, I have to put an index hint in the query to get it to work.

ex

Select * from PRH_EOB (INDEX = IX_PayDate)

WHERE (MemberId = @.MemberId OR @.MemberId = 'ALL')

and (disenr_st = @.Disenroll or @.Disenroll = 'ALL')

and (LOB in (Select * from SplitList(',',@.LOB) as ListItem) or @.LOB = 'ALL')

and (Groupid in (Select * from SplitList(',',@.GroupList) as ListItem) or @.Grouplist = 'ALL')

and paydate between @.From and @.TO

order by Slastname, sFirstname, claimid, linenum