Query, help with where clause to reduce resultset

I need some help with the @.LanguageId part of this query to exclude/include
rows to be returned.
ctbl_content_rel can contain rows with different language ids, e.g 'dk' and
'nn'.
If rows with the specified @.LanguageId exists, then I want only those rows,
otherwise I want only the 'nn' rows (a fallback mechanism).
Now the query returns all the rows for both the specified @.LanguageId and
those with @.LanguageId = 'nn'. Any ideas?
SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
FROM ctbl_content_rel CONTENTREL
JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
WHERE CONTENT.pk_content = @.ContentId
AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid = 'NN')
/tedHow about this?
SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
FROM ctbl_content_rel CONTENTREL
JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
WHERE CONTENT.pk_content = @.ContentId
AND CONTENTREL.languageId = CASE WHEN @.LanguageId = '' THEN 'NN' ELSE
@.LanguageId END
HTH,
Pierre
/"\ ASCII Ribbon Campaign
\ /
X Against HTML
/ \ in e-mail & news|||I'm afraid it will not solve the problem. @.LanguageId will always be
something as input parameter. The clue is that I want the 'nn' rows if the
specified 'dk' rows does not exist in the database.
/ted :)
"Pierre Albisser" <pierre.no_spam@.albisser.de> skrev i melding
news:%23079C5GXFHA.4032@.tk2msftngp13.phx.gbl...
> How about this?
> SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> FROM ctbl_content_rel CONTENTREL
> JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
> WHERE CONTENT.pk_content = @.ContentId
> AND CONTENTREL.languageId = CASE WHEN @.LanguageId = '' THEN 'NN' ELSE
> @.LanguageId END
> --
> HTH,
> Pierre
> /"\ ASCII Ribbon Campaign
> \ /
> X Against HTML
> / \ in e-mail & news|||Try,
if exists(select * from ctbl_content_rel CONTENTREL JOIN ctbl_content
CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content WHERE
CONTENT.pk_content = @.ContentId AND (CONTENTREL.languageId = @.LanguageId))
select distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
from ctbl_content_rel CONTENTREL JOIN ctbl_content CONTENT ON
CONTENTREL.fk_content = CONTENT.pk_content WHERE CONTENT.pk_content =
@.ContentId AND (CONTENTREL.languageId = @.LanguageId)
else
select distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
from ctbl_content_rel CONTENTREL JOIN ctbl_content CONTENT ON
CONTENTREL.fk_content = CONTENT.pk_content WHERE CONTENT.pk_content =
@.ContentId AND (CONTENTREL.languageid = 'NN')
AMB
"ted" wrote:

> I need some help with the @.LanguageId part of this query to exclude/includ
e
> rows to be returned.
> ctbl_content_rel can contain rows with different language ids, e.g 'dk' an
d
> 'nn'.
> If rows with the specified @.LanguageId exists, then I want only those rows
,
> otherwise I want only the 'nn' rows (a fallback mechanism).
> Now the query returns all the rows for both the specified @.LanguageId and
> those with @.LanguageId = 'nn'. Any ideas?
> SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> FROM ctbl_content_rel CONTENTREL
> JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
> WHERE CONTENT.pk_content = @.ContentId
> AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid = 'NN')
> /ted
>
>|||Hi Ted
Probable you can try this way
SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
FROM ctbl_content_rel CONTENTREL
JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
WHERE CONTENT.pk_content = @.ContentId
AND CONTENTREL.languageId = ISNULL(@.LanguageId,'NN')
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"ted" wrote:

> I need some help with the @.LanguageId part of this query to exclude/includ
e
> rows to be returned.
> ctbl_content_rel can contain rows with different language ids, e.g 'dk' an
d
> 'nn'.
> If rows with the specified @.LanguageId exists, then I want only those rows
,
> otherwise I want only the 'nn' rows (a fallback mechanism).
> Now the query returns all the rows for both the specified @.LanguageId and
> those with @.LanguageId = 'nn'. Any ideas?
> SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> FROM ctbl_content_rel CONTENTREL
> JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
> WHERE CONTENT.pk_content = @.ContentId
> AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid = 'NN')
> /ted
>
>|||Thanks :) It seems to be a way to go.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> skrev i melding
news:2E7D1FD3-54AC-4A40-87E4-8EA9EEFF5F6F@.microsoft.com...
> Try,
> if exists(select * from ctbl_content_rel CONTENTREL JOIN ctbl_content
> CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content WHERE
> CONTENT.pk_content = @.ContentId AND (CONTENTREL.languageId = @.LanguageId))
> select distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> from ctbl_content_rel CONTENTREL JOIN ctbl_content CONTENT ON
> CONTENTREL.fk_content = CONTENT.pk_content WHERE CONTENT.pk_content =
> @.ContentId AND (CONTENTREL.languageId = @.LanguageId)
> else
> select distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> from ctbl_content_rel CONTENTREL JOIN ctbl_content CONTENT ON
> CONTENTREL.fk_content = CONTENT.pk_content WHERE CONTENT.pk_content =
> @.ContentId AND (CONTENTREL.languageid = 'NN')
>
> AMB
> "ted" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Yoiu might also want to start following proper data modeling
conventions and stop using prefixes that tell us HOW something is used
in one table, and come with names that tell us what something is in the
whole data model. A prefix that tells us something is a table in a
language that hsa only one data strucutre is absurd.
See ISO-11179 for the standards. Can I assume that you meant to use
the ISO language codes and not a identifier?|||On Thu, 19 May 2005 14:19:58 +0200, ted wrote:

>I need some help with the @.LanguageId part of this query to exclude/include
>rows to be returned.
>ctbl_content_rel can contain rows with different language ids, e.g 'dk' and
>'nn'.
>If rows with the specified @.LanguageId exists, then I want only those rows,
>otherwise I want only the 'nn' rows (a fallback mechanism).
>Now the query returns all the rows for both the specified @.LanguageId and
>those with @.LanguageId = 'nn'. Any ideas?
>SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
>FROM ctbl_content_rel CONTENTREL
>JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
>WHERE CONTENT.pk_content = @.ContentId
>AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid = 'NN')
>/ted
>
Hi ted,
If you're not afraid of using proprietary SQL that won't port to other
databases, you can use this:
SELECT TOP 1 CONTENTREL.fk_filegroup, CONTENTREL.languageid
FROM ctbl_content_rel CONTENTREL
JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
WHERE CONTENT.pk_content = @.ContentId
AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid =
'NN')
ORDER BY CASE WHEN CONTENTREL.languageid = 'NN' THEN 2 ELSE 1 END
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

query tuning

I have a query which is taking 23 sec to run, if i create a temporary table
for subset of resultset of the query and rewrite it using the temporary
table , which is taking only 4 sec. I cant mention my real query, but i
outline it here.
Original Query outline:
select pacct from (select pacct, qacct from tableA
where pid = '123456' and date > @.date) a
where qacct in (select qacct from TableB where groupid = 'asdfa' )
Modified query outline:
select pacct, qacct into #tp from tableA
where pid = '123456' and date > @.date
select pacct from #tp
where qacct in (select qacct from TableB where groupid = 'asdfa' )
TableA has 12 million recs , Table B has half a million recs.
I used inner join too, there is no improvment. From this can anyone guess
what is wrong , with optimiser or query.
Thanks,
Subbu.
Try this instead:
select pacct from tableA
where pid = '123456'
and date > @.date
and exists
(select *
from TableB
where groupid = 'asdfa'
and TableB.qacct = TableA.qacct)
If that doesn't work, post DDL for your tables, including all constraints
and indexes.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
news:eFvWDbCzEHA.828@.TK2MSFTNGP10.phx.gbl...
> I have a query which is taking 23 sec to run, if i create a temporary
table
> for subset of resultset of the query and rewrite it using the temporary
> table , which is taking only 4 sec. I cant mention my real query, but i
> outline it here.
> Original Query outline:
> select pacct from (select pacct, qacct from tableA
> where pid = '123456' and date > @.date) a
> where qacct in (select qacct from TableB where groupid = 'asdfa' )
> Modified query outline:
> select pacct, qacct into #tp from tableA
> where pid = '123456' and date > @.date
> select pacct from #tp
> where qacct in (select qacct from TableB where groupid = 'asdfa' )
> TableA has 12 million recs , Table B has half a million recs.
> I used inner join too, there is no improvment. From this can anyone guess
> what is wrong , with optimiser or query.
> Thanks,
> Subbu.
>
>
>
|||It is taking more than 150 sec and still going i stopped it.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:edXuRLEzEHA.2656@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Try this instead:
>
> select pacct from tableA
> where pid = '123456'
> and date > @.date
> and exists
> (select *
> from TableB
> where groupid = 'asdfa'
> and TableB.qacct = TableA.qacct)
>
> If that doesn't work, post DDL for your tables, including all constraints
> and indexes.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
> news:eFvWDbCzEHA.828@.TK2MSFTNGP10.phx.gbl...
> table
guess
>
|||What are your indexes on the two tables?
-Sue
On Wed, 17 Nov 2004 10:23:06 -0600, "Subbaiahd"
<subbaiahd@.hotmail.com> wrote:

>It is taking more than 150 sec and still going i stopped it.
>"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
>news:edXuRLEzEHA.2656@.TK2MSFTNGP14.phx.gbl...
>guess
>

query to find the top 3 in each type

Hi

I need help in finding the query which will provide the following resultset from the below table..

Table :
create table product_stocks(product_id int , product_type varchar(20) , no_of_units int)

Data:
insert into product_stocks values(1,'A',30)
insert into product_stocks values(2,'A',70)
insert into product_stocks values(3,'A',60)
insert into product_stocks values(4,'A',40)
insert into product_stocks values(1,'B',90)
insert into product_stocks values(2,'B',60)
insert into product_stocks values(3,'B',70)
insert into product_stocks values(4,'B',40)
insert into product_stocks values(1,'C',40)
insert into product_stocks values(2,'C',50)
insert into product_stocks values(3,'C',80)
insert into product_stocks values(4,'C',90)

Result Set:
product_type product_id no_of_units
----- ---- -----
A 2 70
A 3 60
A 4 40
B 1 90
B 3 70
B 2 60
C 4 90
C 3 80
C 2 50

i.e The result set gives the top 3 products in each product_type based on the no_of_units.

thanksselect * from product_stocks where product_id in (select top 3 product_id from product_stocks group by product_id )order by product_type,no_of_units desc|||harshal, fortunately for you, your solution has a wee flaw

by the way, did you not notice that this was another RFH post?

:)

RFH = request for homework|||Hi harshal,

Thanks for providing the query.
It was very helpful and met my requirement.

thanks|||harshal, fortunately for you, your solution has a wee flaw

by the way, did you not notice that this was another RFH post?

:)

RFH = request for homework

yeah I thought it would be a RFH..:mad: .

can u please enlighten me on the flaw part please...|||take a look at the subquery

you are grouping on product_id and then taking the top 3 of them

the top three based on what? there's no ORDER BY!!!!|||take a look at the subquery

you are grouping on product_id and then taking the top 3 of them

the top three based on what? there's no ORDER BY!!!!

OHH!! :confused:
I m getting lazy day by day.. need to spend more time on the forums i guess..;)

thanks for pointing out..

harshal|||Hi harshal

I tested the query , but the result is not correct .
It provides the result set for the product_id 1 , 2 , 3 in each product_type and not the
top 3 in each product_type based on no_of_units

thanks|||arjun, try this --select one.product_type
, one.product_id
, one.no_of_units
from daTable as one
inner
join daTable as two
on two.product_type = one.product_type
and two.no_of_units >= one.no_of_units
group
by one.product_type
, one.product_id
, one.no_of_units
having count(*) <= 3 and be sure you can explain it when your teacher asks you how you got it|||Hi

Even if order by is used in the sub query, it will give the top 3 product_id across all the product_type
But what i need is the top 3 from each of the product_type .

thanks|||Even if order by is used in the sub query, it will give the top 3 product_id across all the product_typeno, not if it's a correlated subquery

But what i need is the top 3 from each of the product_typedid you try my query?|||Hi r937 ,

I tried your query. It returns the top 3 product_id from all product_types.
I need another help.
In the result set , the order of the result set varies for each product_type.

product_type product_id no_of_units
----- ---- -----
A 2 70
A 3 60
A 4 40
B 1 90
B 2 60
B 3 70
C 2 50
C 3 80
C 4 90

How to modify this so that the no_of_units for each product_type is in the descending order.

thanks.|||I tried your query. It returns the top 3 product_id from all product_types.you could not possibly have tried it

here is what it produces:A 2 70
A 3 60
A 4 40
B 1 90
B 2 60
B 3 70
C 2 50
C 3 80
C 4 90this is exactly what you asked for|||Hi r937,

I am getting the same result as you have posted.

The Result set is here :

product_type product_id no_of_units
----- ---- -----
A 2 70
A 3 60
A 4 40
B 1 90
B 2 60
B 3 70
C 2 50
C 3 80
C 4 90

In this result set , the no_of_units for product_type 'A' is in descending order,
but the no_of_units for product_type 'B' and 'C' is not in descending order.

What i seek is to get the no_of_units in descending order for each of the product_types.

thanks|||look up ORDER BY in your manual

:)|||look up ORDER BY in your manual

:)
Firstly you must look up ORDER By in manual as r937 said ,thats for your knowledge..
well,this time just try this to get your results..

select one.product_type
, one.product_id
, one.no_of_units
from product_stocks as one
inner
join product_stocks as two
on two.product_type = one.product_type
and two.no_of_units >= one.no_of_units

group
by one.product_type
, one.product_id
, one.no_of_units

having count(*) <= 3 order by one.product_type,one.no_of_units desc

Joydeep|||This is coming pretty close to baby-sitting. arjun, you need to become familiar with books online. If you can't find your answer there, or you don't understand something, then post a question.