Jump to content
  • Advertisement
Sign in to follow this  
EvilNando

[web] Help with a simple SELECT

This topic is 3771 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

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
Advertisement
SELECT ID FROM FOO WHERE VALUE > 50;

I think you will get a hard time with your database exams.

Share this post


Link to post
Share on other sites
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
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
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
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
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
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
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
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

Participate in the game development conversation and more when you create an account on GameDev.net!

Sign me up!