Subqery in SQL doesn't work...need help with that!

[stetocina]

This is the query I wrote:

USE TClient
GO
SELECT ID_stat, dist, ID_line
FROM dbo.K_lines
WHERE (dist, ID_line)
IN
( SELECT MAX(dist), ID_line
FROM dbo.K_lines
GROUP BY ID_line
)
GO

Ok now the bacgrount, I have this situation

ID_line, ID_stat, dist
1 A 0
1 B 12
1 F 23
1 G 34
2 R 0
2 H 33
2 P 45

this is the table and I need to get the maximum dist for every line, so the result should be

ID_line, ID_stat, dist
1 G 34
2 P 45

Now, I get the error message in my SQL Query analyzer:
Incorrect syntax near ','.

The problem is that I know all of the keywords and column names... are correct...but subqery won't work, why...

I am desperate, please help

stetocina

p.s. I am working with MS SQL Server

 

[tlbroadbent]

Yo can't check for 2 values with the IN clause. The following should work.

SELECT
k.ID_stat, k.dist, k.ID_line
FROM dbo.K_lines k
WHERE dist
IN (SELECT MAX(dist)
FROM dbo.K_lines
WHERE ID_line = k.ID_line
GROUP BY ID_line) Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm