Interesting SQL query requirement for <SELECT> menu

Hi All

Wondered if you could help me with the below query.

I have 1 simple table called STOCKCATS that consists of 2 fields.

These fields are called CATID and LEVEL.

The contents of this table are as follows:

CATID LEVEL
cat01 <nothing>
cat02 <nothing>
cat03 cat01
cat04 <nothing>
cat05 cat01
cat06 cat02
cat07 cat04
etc.. etc...

The way this table works is that I have an ASP page that allows the user to
create a stock category at 2 levels, category level and sub-category level.

When I file the entered data into the table, if the user has chosen to
create a category level stock category then the LEVEL field is left blank
and if they chose to create a sub-category level category then I post the
relevant category level stock category code in the LEVEL field. For
example, in the above list cat01 is a category level stock category and
cat05 is a sub-category as it is a sub-category of cat01.

My query is that I want to populate a simple HTML <SELECT> menu (using ASP),
but instead of it being a straightforward 'select catid from stockcats order
by catid', I want to group this list into some kind of order, eg:

instead of:

cat01 <nothing> << I need to bring back this 2nd column so that I can
do a simple IF THEN in asp to indent sub-cats
cat02 <nothing>
cat03 cat01
cat04 <nothing>
cat05 cat01
cat06 cat02
cat07 cat04

I would like

cat01 <nothing> << ditto
cat03 cat01
cat05 cat01
cat02 <nothing>
cat06 cat02
cat04 <nothing>
cat07 cat04

Do you know if this is possible in pure SQL (I must confess that I'm using
MySQL, but I would have thought the SQL syntax would be the same if it is
possible) or a combo of ASP & SQL?

Thanks

RobbieOn Mon, 7 Nov 2005 14:45:41 -0000, Astra wrote:

>Hi All
>Wondered if you could help me with the below query.
>I have 1 simple table called STOCKCATS that consists of 2 fields.
>These fields are called CATID and LEVEL.
>The contents of this table are as follows:
>CATID LEVEL
>cat01 <nothing>
>cat02 <nothing>
>cat03 cat01
>cat04 <nothing>
>cat05 cat01
>cat06 cat02
>cat07 cat04
>etc.. etc...
>The way this table works is that I have an ASP page that allows the user to
>create a stock category at 2 levels, category level and sub-category level.
>When I file the entered data into the table, if the user has chosen to
>create a category level stock category then the LEVEL field is left blank
>and if they chose to create a sub-category level category then I post the
>relevant category level stock category code in the LEVEL field. For
>example, in the above list cat01 is a category level stock category and
>cat05 is a sub-category as it is a sub-category of cat01.

Hi Robbie,

I'm not too happy with this design. Categories are not the same thing as
sub-categories, so you shouldn't lump them together in the same table.

CREATE TABLE Categories
(CatName varchar(10) NOT NULL,
PRIMARY KEY (CatName)
)
CREATE TABLE SubCategories
(SubCatName varchar(10) NOT NULL,
CatName varchar(10) NOT NULL,
PRIMARY KEY (SubCatName),
FOREIGN KEY (CatName) REFERENCES Categories (CatName)
)

>My query is that I want to populate a simple HTML <SELECT> menu (using ASP),
>but instead of it being a straightforward 'select catid from stockcats order
>by catid', I want to group this list into some kind of order, eg:
>instead of:
>cat01 <nothing> << I need to bring back this 2nd column so that I can
>do a simple IF THEN in asp to indent sub-cats
>cat02 <nothing>
>cat03 cat01
>cat04 <nothing>
>cat05 cat01
>cat06 cat02
>cat07 cat04
>I would like
>cat01 <nothing> << ditto
>cat03 cat01
>cat05 cat01
>cat02 <nothing>
>cat06 cat02
>cat04 <nothing>
>cat07 cat04
>Do you know if this is possible in pure SQL (I must confess that I'm using
>MySQL, but I would have thought the SQL syntax would be the same if it is
>possible) or a combo of ASP & SQL?

If you change the design as I suggest, then it's as simple as

SELECT CatName, NULL AS SubCatName
FROM Categories
UNION ALL
SELECT CatName, SubCatName
FROM SubCategories
ORDER BY CatName, SubCatName

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Astra (No@.Spam.com) writes:
> I would like
> cat01 <nothing> << ditto
> cat03 cat01
> cat05 cat01
> cat02 <nothing>
> cat06 cat02
> cat04 <nothing>
> cat07 cat04
> Do you know if this is possible in pure SQL (I must confess that I'm using
> MySQL, but I would have thought the SQL syntax would be the same if it is
> possible) or a combo of ASP & SQL?

I believe this query would work in SQL Server:

SELECT CATID, LEVEL
FROM STOCKCATS
ORDER BY coalesce(LEVEL, CATID), LEVEL

But I don't think it this conforms to ANSI standards, so it may not run
in MySQL.

If you want help with MySQL, you are probably better off asking in
comp.databases.mysql or some other MySQL forum.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Many thanks guys

Apologies for multi-post.

Rgds Robbie

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9707F156E1626Yazorman@.127.0.0.1...
Astra (No@.Spam.com) writes:
> I would like
> cat01 <nothing> << ditto
> cat03 cat01
> cat05 cat01
> cat02 <nothing>
> cat06 cat02
> cat04 <nothing>
> cat07 cat04
> Do you know if this is possible in pure SQL (I must confess that I'm using
> MySQL, but I would have thought the SQL syntax would be the same if it is
> possible) or a combo of ASP & SQL?

I believe this query would work in SQL Server:

SELECT CATID, LEVEL
FROM STOCKCATS
ORDER BY coalesce(LEVEL, CATID), LEVEL

But I don't think it this conforms to ANSI standards, so it may not run
in MySQL.

If you want help with MySQL, you are probably better off asking in
comp.databases.mysql or some other MySQL forum.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Interesting SQL query requirement for <SELECT> menu

Hi All
Wondered if you could help me with the below query.
I have 1 simple table called STOCKCATS that consists of 2 fields.
These fields are called CATID and LEVEL.
The contents of this table are as follows:
CATID LEVEL
cat01 <nothing>
cat02 <nothing>
cat03 cat01
cat04 <nothing>
cat05 cat01
cat06 cat02
cat07 cat04
etc.. etc...
The way this table works is that I have an ASP page that allows the user to
create a stock category at 2 levels, category level and sub-category level.
When I file the entered data into the table, if the user has chosen to
create a category level stock category then the LEVEL field is left blank
and if they chose to create a sub-category level category then I post the
relevant category level stock category code in the LEVEL field. For
example, in the above list cat01 is a category level stock category and
cat05 is a sub-category as it is a sub-category of cat01.
My query is that I want to populate a simple HTML <SELECT> menu (using ASP),
but instead of it being a straightforward 'select catid from stockcats order
by catid', I want to group this list into some kind of order, eg:
instead of:
cat01 <nothing> << I need to bring back this 2nd column so that I can
do a simple IF THEN in asp to indent sub-cats
cat02 <nothing>
cat03 cat01
cat04 <nothing>
cat05 cat01
cat06 cat02
cat07 cat04
I would like
cat01 <nothing> << ditto
cat03 cat01
cat05 cat01
cat02 <nothing>
cat06 cat02
cat04 <nothing>
cat07 cat04
Do you know if this is possible in pure SQL (I must confess that I'm using
MySQL, but I would have thought the SQL syntax would be the same if it is
possible) or a combo of ASP & SQL?
Thanks
Robbiecreate table #tbl (c1 int, c2 int)
insert into #tbl
select 1 ,null
union select 3,1
union select 5,1
union select 2,null
union select 6,2
union select 4,null
union select 7,4
select * from #tbl
order by isnull(c2,c1),c1
"Astra" <No@.Spam.com> wrote in message
news:ONC3ao64FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Hi All
> Wondered if you could help me with the below query.
> I have 1 simple table called STOCKCATS that consists of 2 fields.
> These fields are called CATID and LEVEL.
> The contents of this table are as follows:
> CATID LEVEL
> cat01 <nothing>
> cat02 <nothing>
> cat03 cat01
> cat04 <nothing>
> cat05 cat01
> cat06 cat02
> cat07 cat04
> etc.. etc...
> The way this table works is that I have an ASP page that allows the user
to
> create a stock category at 2 levels, category level and sub-category
level.
> When I file the entered data into the table, if the user has chosen to
> create a category level stock category then the LEVEL field is left blank
> and if they chose to create a sub-category level category then I post the
> relevant category level stock category code in the LEVEL field. For
> example, in the above list cat01 is a category level stock category and
> cat05 is a sub-category as it is a sub-category of cat01.
> My query is that I want to populate a simple HTML <SELECT> menu (using
ASP),
> but instead of it being a straightforward 'select catid from stockcats
order
> by catid', I want to group this list into some kind of order, eg:
> instead of:
> cat01 <nothing> << I need to bring back this 2nd column so that I
can
> do a simple IF THEN in asp to indent sub-cats
> cat02 <nothing>
> cat03 cat01
> cat04 <nothing>
> cat05 cat01
> cat06 cat02
> cat07 cat04
> I would like
> cat01 <nothing> << ditto
> cat03 cat01
> cat05 cat01
> cat02 <nothing>
> cat06 cat02
> cat04 <nothing>
> cat07 cat04
> Do you know if this is possible in pure SQL (I must confess that I'm using
> MySQL, but I would have thought the SQL syntax would be the same if it is
> possible) or a combo of ASP & SQL?
> Thanks
> Robbie
>
>|||Hi Moshe
Many thanks for your prompt reply, but how this would work?
Where does this relate to my STOCKCATS table'
Rdgs Robbie
<Moshe> wrote in message news:%23xbjsy64FHA.4076@.tk2msftngp13.phx.gbl...
create table #tbl (c1 int, c2 int)
insert into #tbl
select 1 ,null
union select 3,1
union select 5,1
union select 2,null
union select 6,2
union select 4,null
union select 7,4
select * from #tbl
order by isnull(c2,c1),c1
"Astra" <No@.Spam.com> wrote in message
news:ONC3ao64FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Hi All
> Wondered if you could help me with the below query.
> I have 1 simple table called STOCKCATS that consists of 2 fields.
> These fields are called CATID and LEVEL.
> The contents of this table are as follows:
> CATID LEVEL
> cat01 <nothing>
> cat02 <nothing>
> cat03 cat01
> cat04 <nothing>
> cat05 cat01
> cat06 cat02
> cat07 cat04
> etc.. etc...
> The way this table works is that I have an ASP page that allows the user
to
> create a stock category at 2 levels, category level and sub-category
level.
> When I file the entered data into the table, if the user has chosen to
> create a category level stock category then the LEVEL field is left blank
> and if they chose to create a sub-category level category then I post the
> relevant category level stock category code in the LEVEL field. For
> example, in the above list cat01 is a category level stock category and
> cat05 is a sub-category as it is a sub-category of cat01.
> My query is that I want to populate a simple HTML <SELECT> menu (using
ASP),
> but instead of it being a straightforward 'select catid from stockcats
order
> by catid', I want to group this list into some kind of order, eg:
> instead of:
> cat01 <nothing> << I need to bring back this 2nd column so that I
can
> do a simple IF THEN in asp to indent sub-cats
> cat02 <nothing>
> cat03 cat01
> cat04 <nothing>
> cat05 cat01
> cat06 cat02
> cat07 cat04
> I would like
> cat01 <nothing> << ditto
> cat03 cat01
> cat05 cat01
> cat02 <nothing>
> cat06 cat02
> cat04 <nothing>
> cat07 cat04
> Do you know if this is possible in pure SQL (I must confess that I'm using
> MySQL, but I would have thought the SQL syntax would be the same if it is
> possible) or a combo of ASP & SQL?
> Thanks
> Robbie
>
>|||instead
select * from #tbl
order by isnull(c2,c1),c1
I would use
select * from #tbl
order by isnull(c2,c1), c2
to be sure main level is always the first and sub items are below.
Little problem, if you need sort items alphabetically. Like:
Alpha
C...
R...
Beta
F...
R...
G...
select A.CATID, A.LEVEL, A.TEXT, (select B.TEXT from STOCKCATS B where
B.CATID=A.LEVEL) ParentText
from STOCKCATS A
order by ISNULL(ParentText, Text), c2, Text
but sorting by using ISNULL( varchar, ...) will be very slow. I think the
best way is separate it into 2 SELECTS. One for root items, and other one fo
r
getting subitems for each root item.|||On Mon, 7 Nov 2005 14:45:04 -0000, Astra wrote:

>Hi All
>Wondered if you could help me with the below query.
Hi Robbie,
I answered the exact same question in comp.databases.ms-sqlserver about
an hour ago.
Please do not post the same message independantly to several groups.
Pick one group; post there. Or, if you really think that the message is
appropriate for two groups, post ONE SINGLE message and crosspost it to
both groups. That way, all answers will appear in both groups as well,
which saves us the time to answer a message that already has been
replied to in another group.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Many thanks guys.
Apologies for the multi-post.
Rgds Robbie
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:21qvm1p9qfsems6v8b58m3m1e2ir0dfp47@.
4ax.com...
On Mon, 7 Nov 2005 14:45:04 -0000, Astra wrote:

>Hi All
>Wondered if you could help me with the below query.
Hi Robbie,
I answered the exact same question in comp.databases.ms-sqlserver about
an hour ago.
Please do not post the same message independantly to several groups.
Pick one group; post there. Or, if you really think that the message is
appropriate for two groups, post ONE SINGLE message and crosspost it to
both groups. That way, all answers will appear in both groups as well,
which saves us the time to answer a message that already has been
replied to in another group.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Interesting SELECT question

Hello
Does ANSI SQL support conditional selects ?
I have a table with say 15 fields
tblTale1
ID
F1
F2
F3
F4
F5
F6
.
.
.
F15
What I want to do is, add all the Fields from F1 to F15 and divide that
total by the total number of fields which were more than 0.
I know how to sum you can use Sum(f1+f2+f3...) , but how can I get the
number of fields which is more than 0 ?
Even if I put 15 conditions, if f1 > 0 or f2 > 0 or f3 > 0, it still doesn't
give me the # of feilds for that one record which is greater than zero...
I can't think of any solution except to put these fields in another table.
Let me know if i'm missing something.
ThanksI don't understand you. Can you post DDL+ sample data + expected result?
Did you mean ROWS not FIELDS?
"ms news group" <hemang@.hemang.net> wrote in message
news:OcBE7DigFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Hello
> Does ANSI SQL support conditional selects ?
> I have a table with say 15 fields
> tblTale1
> ID
> F1
> F2
> F3
> F4
> F5
> F6
> .
> .
> .
> F15
>
> What I want to do is, add all the Fields from F1 to F15 and divide that
> total by the total number of fields which were more than 0.
> I know how to sum you can use Sum(f1+f2+f3...) , but how can I get the
> number of fields which is more than 0 ?
>
> Even if I put 15 conditions, if f1 > 0 or f2 > 0 or f3 > 0, it still
doesn't
> give me the # of feilds for that one record which is greater than zero...
>
> I can't think of any solution except to put these fields in another table.
> Let me know if i'm missing something.
> Thanks
>
>|||Your table design is very questionable but assuming no NULLs and no
negatives, try this:
SELECT
(f1 + f2 + f3 + f4 + f5 + f6 + f7 + f8 + f9
+ f10 + f11 + f12 + f13 + f14 + f15)
/
(SIGN(f1) + SIGN(f2) + SIGN(f3) + SIGN(f4) + SIGN(f5)
+ SIGN(f6) + SIGN(f7) + SIGN(f8) + SIGN(f9) + SIGN(f10)
+ SIGN(f11) + SIGN(f12) + SIGN(f13) + SIGN(f14) + SIGN(f15))
FROM Table1 ;
If you have to cope with negatives then change SIGN(x) to ABS(SIGN(x)).
In general for conditional execution take a look at the CASE
expression.
David Portas
SQL Server MVP
--|||you could use case
select (f1 + f2 + f3 + f4) / case when f1 = 0 and f2 = 0 and f3 = 0 and f4 =
0 then 1 else case when f1 = 0 then 0 else 1 end + case when f2 = 0 then 0
else 1 end + case when f3 = 0 then 0 else 1 end + case when f4 = 0 then 0
else 1 end end from tableName
"ms news group" <hemang@.hemang.net> wrote in message
news:OcBE7DigFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Hello
> Does ANSI SQL support conditional selects ?
> I have a table with say 15 fields
> tblTale1
> ID
> F1
> F2
> F3
> F4
> F5
> F6
> .
> .
> .
> F15
>
> What I want to do is, add all the Fields from F1 to F15 and divide that
> total by the total number of fields which were more than 0.
> I know how to sum you can use Sum(f1+f2+f3...) , but how can I get the
> number of fields which is more than 0 ?
>
> Even if I put 15 conditions, if f1 > 0 or f2 > 0 or f3 > 0, it still
doesn't
> give me the # of feilds for that one record which is greater than zero...
>
> I can't think of any solution except to put these fields in another table.
> Let me know if i'm missing something.
> Thanks
>
>|||Here are a couple of options:
select id,
(f1*s1 + f2*s2 + f3*s3 + ... + f15*s15)
/ (s1 + s2 + s3 + ... + s15) as avgpos
from (select *,
1+sign(sign(f1) -1) as s1,
1+sign(sign(f2) -1) as s2,
1+sign(sign(f3) -1) as s3,
..
1+sign(sign(f15)-1) as s15
from (select id,
isnull(f1, 0) as f1,
isnull(f2, 0) as f2,
isnull(f3, 0) as f3,
..
isnull(f15, 0) as f15
from t1) as d1) as d2
select id, avg(val) as avgpos
from (select id, n,
case n
when 1 then f1
when 2 then f2
when 3 then f3
..
when 15 then f15
end as val
from t1,
(select 1 as n
union all select 2
union all select 3
..
union all select 15) as nums) as d
where val > 0
group by id
BG, SQL Server MVP
www.SolidQualityLearning.com
"ms news group" <hemang@.hemang.net> wrote in message
news:OcBE7DigFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Hello
> Does ANSI SQL support conditional selects ?
> I have a table with say 15 fields
> tblTale1
> ID
> F1
> F2
> F3
> F4
> F5
> F6
> .
> .
> .
> F15
>
> What I want to do is, add all the Fields from F1 to F15 and divide that
> total by the total number of fields which were more than 0.
> I know how to sum you can use Sum(f1+f2+f3...) , but how can I get the
> number of fields which is more than 0 ?
>
> Even if I put 15 conditions, if f1 > 0 or f2 > 0 or f3 > 0, it still
> doesn't give me the # of feilds for that one record which is greater than
> zero...
>
> I can't think of any solution except to put these fields in another table.
> Let me know if i'm missing something.
> Thanks
>
>

Interesting SELECT question

Hello
Does ANSI SQL support conditional selects ?
I have a table with say 15 fields
tblTale1
ID
F1
F2
F3
F4
F5
F6
.
.
.
F15
What I want to do is, add all the Fields from F1 to F15 and divide that
total by the total number of fields which were more than 0.
I know how to sum you can use Sum(f1+f2+f3...) , but how can I get the
number of fields which is more than 0 ?
Even if I put 15 conditions, if f1 > 0 or f2 > 0 or f3 > 0, it still doesn't
give me the # of feilds for that one record which is greater than zero...
I can't think of any solution except to put these fields in another table.
Let me know if i'm missing something.
ThanksI don't understand you. Can you post DDL+ sample data + expected result?
Did you mean ROWS not FIELDS?
"ms news group" <hemang@.hemang.net> wrote in message
news:OcBE7DigFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Hello
> Does ANSI SQL support conditional selects ?
> I have a table with say 15 fields
> tblTale1
> ID
> F1
> F2
> F3
> F4
> F5
> F6
> .
> .
> .
> F15
>
> What I want to do is, add all the Fields from F1 to F15 and divide that
> total by the total number of fields which were more than 0.
> I know how to sum you can use Sum(f1+f2+f3...) , but how can I get the
> number of fields which is more than 0 ?
>
> Even if I put 15 conditions, if f1 > 0 or f2 > 0 or f3 > 0, it still
doesn't
> give me the # of feilds for that one record which is greater than zero...
>
> I can't think of any solution except to put these fields in another table.
> Let me know if i'm missing something.
> Thanks
>
>|||Your table design is very questionable but assuming no NULLs and no
negatives, try this:
SELECT
(f1 + f2 + f3 + f4 + f5 + f6 + f7 + f8 + f9
+ f10 + f11 + f12 + f13 + f14 + f15)
/
(SIGN(f1) + SIGN(f2) + SIGN(f3) + SIGN(f4) + SIGN(f5)
+ SIGN(f6) + SIGN(f7) + SIGN(f8) + SIGN(f9) + SIGN(f10)
+ SIGN(f11) + SIGN(f12) + SIGN(f13) + SIGN(f14) + SIGN(f15))
FROM Table1 ;
If you have to cope with negatives then change SIGN(x) to ABS(SIGN(x)).
In general for conditional execution take a look at the CASE
expression.
--
David Portas
SQL Server MVP
--|||you could use case
select (f1 + f2 + f3 + f4) / case when f1 = 0 and f2 = 0 and f3 = 0 and f4 =0 then 1 else case when f1 = 0 then 0 else 1 end + case when f2 = 0 then 0
else 1 end + case when f3 = 0 then 0 else 1 end + case when f4 = 0 then 0
else 1 end end from tableName
"ms news group" <hemang@.hemang.net> wrote in message
news:OcBE7DigFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Hello
> Does ANSI SQL support conditional selects ?
> I have a table with say 15 fields
> tblTale1
> ID
> F1
> F2
> F3
> F4
> F5
> F6
> .
> .
> .
> F15
>
> What I want to do is, add all the Fields from F1 to F15 and divide that
> total by the total number of fields which were more than 0.
> I know how to sum you can use Sum(f1+f2+f3...) , but how can I get the
> number of fields which is more than 0 ?
>
> Even if I put 15 conditions, if f1 > 0 or f2 > 0 or f3 > 0, it still
doesn't
> give me the # of feilds for that one record which is greater than zero...
>
> I can't think of any solution except to put these fields in another table.
> Let me know if i'm missing something.
> Thanks
>
>|||Here are a couple of options:
select id,
(f1*s1 + f2*s2 + f3*s3 + ... + f15*s15)
/ (s1 + s2 + s3 + ... + s15) as avgpos
from (select *,
1+sign(sign(f1) -1) as s1,
1+sign(sign(f2) -1) as s2,
1+sign(sign(f3) -1) as s3,
...
1+sign(sign(f15)-1) as s15
from (select id,
isnull(f1, 0) as f1,
isnull(f2, 0) as f2,
isnull(f3, 0) as f3,
...
isnull(f15, 0) as f15
from t1) as d1) as d2
select id, avg(val) as avgpos
from (select id, n,
case n
when 1 then f1
when 2 then f2
when 3 then f3
...
when 15 then f15
end as val
from t1,
(select 1 as n
union all select 2
union all select 3
...
union all select 15) as nums) as d
where val > 0
group by id
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"ms news group" <hemang@.hemang.net> wrote in message
news:OcBE7DigFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Hello
> Does ANSI SQL support conditional selects ?
> I have a table with say 15 fields
> tblTale1
> ID
> F1
> F2
> F3
> F4
> F5
> F6
> .
> .
> .
> F15
>
> What I want to do is, add all the Fields from F1 to F15 and divide that
> total by the total number of fields which were more than 0.
> I know how to sum you can use Sum(f1+f2+f3...) , but how can I get the
> number of fields which is more than 0 ?
>
> Even if I put 15 conditions, if f1 > 0 or f2 > 0 or f3 > 0, it still
> doesn't give me the # of feilds for that one record which is greater than
> zero...
>
> I can't think of any solution except to put these fields in another table.
> Let me know if i'm missing something.
> Thanks
>
>

Interesting interview question...........

Hi all,
One small question.
I am having a table named maverick and having 2 fields, tkno(int) and
name(varchar(20)).
The table is filled with 10 entries.
tkno name
1 Zamsheer
2 Thushara
3 Kori
4 Attu
................etc
I want to retrieve the 6th record(any specified) from the table
........that's it.......
But the interesting factor is
U should not mention any field name in the query
don't use a cursor......
no stored procedure, no function... nothing
One single query should give the result......
Regards,
JaisonThe sixth row according to what order? tkno?
select top 1 *
from (
select top 6 *
from <table>
order by tkno asc
) TopSix
order by TopSix.tkno desc
Close enough?
ML
http://milambda.blogspot.com/|||If you really 'cant' use column names, you could use ordinal number of the
column. So, instead of order by tkno you can use order by 1
Offcourse, dont do that in a 'normal' environment.
MC
"ML" <ML@.discussions.microsoft.com> wrote in message
news:622ED66D-6581-4744-9D3B-C9C53BED400B@.microsoft.com...
> The sixth row according to what order? tkno?
> select top 1 *
> from (
> select top 6 *
> from <table>
> order by tkno asc
> ) TopSix
> order by TopSix.tkno desc
> Close enough?
>
> ML
> --
> http://milambda.blogspot.com/|||USe pubs
--2000
DECLARE @.param int
SET @.param = 3
Select j.* FROM Jobs J
WHERE @.param>=(SELECT Count(*) FROM Jobs B
WHERE B.Job_Id <= J.Job_Id )
ORDER By Job_Id
--2005
SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY ContactID DESC) As Rno, * FROM
Person.Contact) T
WHERE T.Rno = 5
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
news:FA6190B9-9A9C-4308-BD9E-91B91F52FD08@.microsoft.com...
> Hi all,
> One small question.
> I am having a table named maverick and having 2 fields, tkno(int) and
> name(varchar(20)).
> The table is filled with 10 entries.
> tkno name
> 1 Zamsheer
> 2 Thushara
> 3 Kori
> 4 Attu
> ................etc
> I want to retrieve the 6th record(any specified) from the table
> ........that's it.......
>
> But the interesting factor is
> U should not mention any field name in the query
> don't use a cursor......
> no stored procedure, no function... nothing
> One single query should give the result......
> Regards,
> Jaison
>|||Thanks...........
"ML" wrote:

> The sixth row according to what order? tkno?
> select top 1 *
> from (
> select top 6 *
> from <table>
> order by tkno asc
> ) TopSix
> order by TopSix.tkno desc
> Close enough?
>
> ML
> --
> http://milambda.blogspot.com/|||The ordinal position, yes. That would fully comply with the requirement. And
I agree - to vulnerable to be of any real use.
ML
http://milambda.blogspot.com/

Interactive sort not working in Tabular View

I am just starting with Sql Server reporting, but I can't get interactive sort to work for columns with aggregate fields.

I am using the following query for DataSet:

SELECT
Store.ID as StoreID,
Store.Name as StoreName,
COUNT(*) as NumReservations,
SUM(Appointment.TotalBeforeTaxes) as Revenue
FROM Store LEFT JOIN Appointment ON Store.ID=Appointment.StoreID
GROUP BY ALL Store.ID, Store.Name
ORDER BY Store.Name ASC

For report, I am using tabular data view. Interactive sorting works great for StoreID, StoreName, but doesn't work for NumReservations and Revenue fields. I turned it on for all 4 columns.

What could be causing this problem?
Figured out the problem... It just doesn't work in FireFox. Seems to work fine in IE.

Interactive sort not working if sub report

Hi,
I am having an issue that when a sub report that contains fields that
are setup to do interactive sort only work if they are displayed as the
parent report. If they are included as a sub report when trying to do
a sort the page just posts back but comes back unsorted.
Is there a work around for this or is it just not possible on SSRS
2005?
Please Help!
Chrischris wrote:
> Hi,
> I am having an issue that when a sub report that contains fields that
> are setup to do interactive sort only work if they are displayed as the
> parent report. If they are included as a sub report when trying to do
> a sort the page just posts back but comes back unsorted.
> Is there a work around for this or is it just not possible on SSRS
> 2005?
> Please Help!
> Chris|||Chris,
I had the same problem with sorting in a sub report, I then changed my
report to not contain a sub report, however I found that when you group
your data in a table using a "Group On" expression, sorting does not
work either. Sorting only appears to work on non-grouped data fields
in a table and not in a sub report.
chris wrote:
> Hi,
> I am having an issue that when a sub report that contains fields that
> are setup to do interactive sort only work if they are displayed as the
> parent report. If they are included as a sub report when trying to do
> a sort the page just posts back but comes back unsorted.
> Is there a work around for this or is it just not possible on SSRS
> 2005?
> Please Help!
> Chris|||my report with sub-report is word well when sorting.

Interactice Sorting confusion!

Well I have a data set with the following fields

1) Territory

2) Product Category

3) Sub-Product Category

4) Total Sales

I created a report that is a List with a Nested Table

The List have the Territory Field (and have a grouping on Territory)

And the nested Table Have the other 3 fields Grouped By Product Category

The table group header have the product category fields

And the table details have the sub-category and Total Sales fields

Now to the problem, I have no problem adding interactive sort on the two fields in the table details rows.

But I cannot add an interactive sort to Territory Field in the list or the Product Category in the table group header.

Is this a limitation in interactive sort, they dont sort on fields used for grouping other fields !!!

I also couldn't really understand the interactive field options, the help doesn't really explain them clearly

What does the Options

1- Data Region or Grouping to Sort

2- Evaluate Sort Expression in this scope

How are they really used I dont get it? I tried many options hoping something will work for the territory or product category fields mentioned above, but nothing work!

I hope I was clear

I will try to be more precise I have the following grouping

Territory | Category | Sub Category | Sales

USA

Bike

Bike Mountain 100

Bike Racing 200

Toy

Toy Barbie 50

Toy He-Man 74

UK

Bike

Bike Mountain 100

Bike Racing 200

Toy

Toy Barbie 50

Toy He-Man 74

UAE

Bike

Bike Mountain 100

Bike Racing 200

Toy

Toy Barbie 50

Toy He-Man 74

Terriroty is actually in a text field in the list, where I have a nested table with the other fields group on category in the table group header

I have easily add interactive sort to the subcategory and sales table files, using the default values for the options

I cannot field the correct option combination to add interactive sort for the territory or category fields