subquery
-
IDRecordNrRecordLinePrice
aaa1110
aaa1225
aaa1313
bbc217
bbc224
a recordnr has many line
i need to amke a query sothat :
the recordnr has price more than a
and in this recordnr the recordline should be at least b?
anyone can helo?
thanx in advance!!
-
select id,Recordnr from table where price >b and a< (select id,recordnr,sum(recordline) from table group by recordnr,ID )
thats my query but that doesnt work:-(
-
What is "a" and what is "b"... they are not in the table you described. I realize there is a bit of a language barrier problem here, but do the best you can... type slowly...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
there is a space between each column!!!
aaa is an id
-
Jeff was objecting to this you wrote
the recordnr has price more than a
and in this recordnr the recordline should be at least b?
What is a and b supposed to mean?
N 56°04'39.16"
E 12°55'05.25" -
Well, as I understand it, "a" and "b" are either variables passed to the code when you run it, or some fixed values hardcoded into the query. Unfortunately, in the example da has switched a and b, so that it becomes even more confusing, but I think it could be something like:
SET @a = 3 /*enter required value*/
SET @b-2 = 20 /*enter required value*/
select t.[id], t.Recordnr
from mytable t
join (select [id], SUM(recordline) as sum_of_lines from mytable group by [id]) as Q on Q.[id]=t.[id]
where t.price > @a
and Q.sum_of_lines >= @b-2
However, I'm not sure whether I understand correctly the requirement about "recordline should be at least..". If this means that sum of all values of recordline for this particular ID is greater or equal to b, then it should work.
Otherwise, please try to describe the requirement in more detail or show us an example of data and intended result.
-
What say the da?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
da - when you catch up to this thread this evening, some suggestions - to make your notational use clear, and hopefully allow us to respond to you more accurately and completely:
1. remember that we use different browsers and that we may not see formatting as you see it.
2. try to present your question in terms of the problem you want to solve. What IS the problem you need to solve? There are many techniques, some better than others. But an answer which solves the wrong problem doesn't help.
3. Please give descriptive names to your data - 'a' and 'b' and 'x' and 'y' work very nicely in Algebra. Not so nicely in database.
4. If some of our responses confuse you, then it's very likely we ARE confused. The more descriptive you can be, the better.
Please confirm that I am correct about what you've said so far:
A. recordnr is an identifier for a 'header' on a multiline form - a bill, an invoice, a packing list, whatever.
B. recordline is an identifier for a 'detail' row in the multiline portion.
C. You reference 'a' as a minimum 'price' - for a 'header' - NOT a detail. This implies an invoice total or some other total value.
D. You reference 'b' as a minimum value for - what? - a specific detail line? a price? a recordnr (aka 'header')?
One of the reasons we ask that you focus on a clear statement of the problem, and then identify whether our responses solve that problem, is that we will think about the problem in a different way than you will - that is the strength of coming to a forum like this. If you don't give us a clear problem, we can't give you a clear answer. I hope this helps.
-
hello
aaa or bbb is the primar key and unique
the a anf b values are number like 3 oder 20:-P
thanx loads for replies:-P
-
aaa or bbb is the primary key and unique???? Why do they show up in your example data more than once?
I still don't understand your questions or your answers.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
why not???
because the recordline and price are different!
i should replace aaa bbb like nr1,nr2,nr3 ect doesnt really matter!
a and b are too fixed valued!!!
-
da,
I'm not sure whether this is only a matter of language, but if you have problems defining the requirements in English, you can try it in German (at least I suppose that you could speak German because of "oder"). I will translate it to English, so that everybody knows what we are talking about 🙂
Please, tell me whether my solution works - and if not, what's wrong.
-
ja freilig:-P
Vladan.. thats a good solution!
thanx loads..
i cant understand what the problem with the expression is ?
but i have problem with the performance cauz i have around 2,8 million id:-P thats kinda hard:-P
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply