[web] Help with a simple SELECT
Author
I want to make a SELECT only the ID's that have all of its entries bigger than the value specified
table FOO
ID int
VALUE int
so if I populate FOO with
ID VALUE
1 100
1 150
2 40
2 100
how can I make a select that returns only the Id that have all of its entries bigger than , lets say 50?
thanks
Quote:Original post by Fase
SELECT ID FROM FOO WHERE VALUE > 50;
I think you will get a hard time with your database exams.
I think you misread the post: SELECT only the ID's that have all of its entries bigger than the value specified. The ID is not unique.
SELECT DISTINCT(ID)
FROM Foo WHERE ID NOT IN (SELECT ID FROM Foo WHERE Value <= 50)
There might be a nicer way of doing it though.
You might also try something like this:
I suspect that would be more efficient than the "in subselect" WanMaster suggested, but I'm not sure. Try both and see which is more efficient with your db and actual data.
select distinct IDfrom FOO fwhere not exists ( select * from FOO fw where fw.ID = f.ID and fw.VALUE < :limit)I suspect that would be more efficient than the "in subselect" WanMaster suggested, but I'm not sure. Try both and see which is more efficient with your db and actual data.
I Stand corrected. Next time I should read better.
Your solution as stated does work, but maybe an inner join can be used. I will try to come up with an example and post it when I find one.
Your solution as stated does work, but maybe an inner join can be used. I will try to come up with an example and post it when I find one.
Author
Quote:Original post by Fase
There might be a nicer way of doing it though.
That is what I was thinking thats why I asked, I mean if the need is to find the ids that have BIGGER values , it would be nice to use a > (bigger than) instead of the contrary
the correct way to do that imo is using the HAVING clause.
HAVING is like the WHERE clause but work on agregates.
you could do something like this :
select id
from table
group by id
having min(value) > 50
HAVING is like the WHERE clause but work on agregates.
you could do something like this :
select id
from table
group by id
having min(value) > 50
Author
To all who posted
BIG THANK YOU
specially for you Mr Grinch
Edit:
Eddy* that worked pretty as well thank you
[Edited by - EvilNando on February 22, 2008 2:08:41 PM]
BIG THANK YOU
specially for you Mr Grinch
Edit:
Eddy* that worked pretty as well thank you
[Edited by - EvilNando on February 22, 2008 2:08:41 PM]
Good call, "having" does seem like it makes more sense and that query is much simpler than the one I suggested. For whatever it's worth, I tried an example using the "not exists (...)" and "having" in Informix, the table has around 160k rows total, 66k unique IDs, and I ended up selecting almost 11k IDs. The query using exists took around 2.6 seconds, the query using having took only about 1.6 seconds. I ran both queries several times and took the min time for each, so that hopefully neither would have a cache advantage. Take that with a grain of salt though. In my experience the performance can really vary depending on the specifics of how your data and the query are really set up, although it seems like Informix is more quirky than other RDBMs.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
