February 7, 2005 at 2:30 pm
I have a table that has several sample depths per location and I am trying to write a query that selects the lowest depth value per location. I am trying to write a subquery and do not understand the right way to write it properly.
If I write
select location_id, min(sample_depth)as 'sample_depth' FROM sample_table
group by location_id
My results show the shallowest depth per location for all locations in the table
However if I want to use this subset to get the rest of the columns in the table and I try a subquery such as
select * from sample_table where exists
(select location_id, min(sample_depth)as 'sample_depth' FROM sample_table
group by location_id)
order by hole_number
I get every record in the table and not just the filtered records the subquery gives me?
What am I doing wrong here?
Thanks for any help you can provide
February 7, 2005 at 2:38 pm
My suggestion: add a unique column on the table. Probably just add an identity column.
That way the select becomes
select * from sample_table where
id_col in (select id_col
FROM sample_table
group by location_id
having min(sample_depth))
order by hole_number
This is just my $0.02.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
February 7, 2005 at 2:54 pm
The problem with your subquery is that it is not correlated to the main select. try this:
select s1.*
from sample_table as s1
Inner Join
(
select location_id, min(sample_depth)as sample_depth
FROM sample_table
group by location_id
) s2
On ( s1.location_id = s2.location_id And
s1.sample_depth = s2.sample_depth )
order by s1.hole_number
February 7, 2005 at 3:13 pm
Hi PW,
Thanks for your help and the example of how to code this kind of query properly.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply