Sign in to follow this  
EvilNando

[web] Help with a simple SELECT

Recommended Posts

EvilNando    96
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

Share this post


Link to post
Share on other sites
Wan    1366
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.

Share this post


Link to post
Share on other sites
Mr Grinch    318
You might also try something like this:
select distinct ID
from FOO f
where 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.

Share this post


Link to post
Share on other sites
Fase    133
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.


Share this post


Link to post
Share on other sites
EvilNando    96
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

Share this post


Link to post
Share on other sites
Eddycharly    437
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

Share this post


Link to post
Share on other sites
EvilNando    96
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]

Share this post


Link to post
Share on other sites
Mr Grinch    318
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.

Share this post


Link to post
Share on other sites
Eddycharly    437
funny, it's almost the double without having.
i suppose it's because with having, there's one table scan, and without having, there are two.

one trick with exists, exists (select 1 ...) is fater than exists (select * ...) because it doesn't need to collect datas from the table for return.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this