SQL query: max() from subset

[Ladyluck]

Hi all; This is an elementary SQL question but with the resources at hand (I am somewhat isolated at the moment)I turn to this forum.
I need to query a database for a series of values e.g.
"select from database column1, column2, column3, for" etc.
Which returns a large set of data. Values from column1 take specific values; i.e. a series of lines with value if 235.7 followed by a series of lines with a value of 285.8. For these subsets values in column2 and/or column3 may be quite variable. I am only interested in the line that contains the maximum value in column2 for any particular subset.
Thank you in advance!

 

[r937]

select column1
     , column2
     , column3 
  from daTable as T
 where column2
     = ( select max(column2)
           from daTable
          where column1 = T.column1 )

r937.com | rudy.ca

 

[Ladyluck]

Thank you r937. Your code worked but didn't produce the results I had hoped for probably because my original post was unclear.
This is what the data table I extract from the database with a simple SQL query looks like:

Depth T°C P(bar) Date
235.7 23.5 11.2 03.04.06
235.7 19.8 11.1 03.04.06
235.7 21.3 10.8 03.04.06
285.8 22.8 11.7 03.04.06
285.8 23.1 11.7 04.04.06
285.8 22.0 11.4 04.04.06

However I want to exract only the lines with the highest T°C value for a given set of depth-values(lines 1 and 5 in this case). So that the resulting table looks like this

Depth T°C P(bar) Date
235.7 23.5 11.2 03.04.06
285.8 23.1 11.7 04.04.06

GO Leafs!

 

[r937]

and why won't my query work? can i see what you tried?

r937.com | rudy.ca

 

[Ladyluck]

ok sorry

select depth,
	temp,
	press,
	date
 from gbase_020 as T
where temp
	= ( select max (temp)
	from gbase_020
	where depth = T.depth
	and temp = T.temp
	and press = T.press
	and date = T.date)

all depth values in the output are replaced by 0.0. Thanks again.

 

[r937]

okay, i think i see how you got confused, but to be honest, it's your own fault for setting up the example using names like "column1, column2, column3"

you don't want to put all the columns into the subquery, because then each row would be distinct -- instead, you want to take the MAX() of a group of rows, namely, all the rows that have the same depth



r937.com | rudy.ca

 

[Ladyluck]

I accept full responsibility! Turning this thread into a SQL-101 course I am now having a problem adding additional conditions. The following works:

select depth,
    temp,
    press,
    date
 from gbase_020 as T
where temp
    = ( select max (temp)
    from gbase_020
    where depth = T.depth)

But it would be nice to look at specific time intervals. I have tried adding:

where date > '2006-04-03' and date < '2006-04-05'

Either before or after the from gbase_020 as T statement and at the end of the query. Thus revealing my lack of understanding exactly how the query works. So for what it is worth thanks again and again , Go Leafs!

 

[r937]

stop with the leafs, already

there are two places that the WHERE clause can go -- in the subquery, or in the outer query

try it both ways and see what you get

i think one way you will find some result rows missing, and i'll give you a hint, the other way is correct

let me know if that doesn't make sense after you try it

r937.com | rudy.ca