Need to retrieve a data row that having minimum sum of 4 red squared values.

i Have done a SP ,but seems to be it was wrong.

select *
select *
from SMST as p
where  p.Source=@Source AND p.Destination=@Destination OR p.Destination2=@Destination AND  [Count]=(Select MIN(sourceRoadCount+middleroadcount+destinationroadcount+x1destinationroad2count as [Count]) from SMST  )

Source and Destination will be given by user on a win form.
sourceRoadCount+middleroadcount+destinationroadcount+x1destinationroad2count are the 4 field that i want to get minimum sum valu.

Attachments l.jpg 173.61 KB
select *
 from SMST as p
  where  p.Source=@Source AND ( p.Destination=@Destination OR p.Destination2=@Destination) AND  (p.sourceRoadCount+p.middleroadcount+p.destinationroadcount+p.x1destinationroad2count)>=4 from SMST
select *
 from SMST as p
  where  p.Source=@Source AND ( p.Destination=@Destination OR p.Destination2=@Destination) AND  (p.sourceRoadCount+p.middleroadcount+p.destinationroadcount+p.x1destinationroad2count)>=4 from SMST

thanx for quick replying dear.

but on last line....

(p.sourceRoadCount+p.middleroadcount+p.destinationroadcount+p.x1destinationroad2count)>=4 from SMST .

this is not what i wont.
what i need is ;
the minimum count of

(p.sourceRoadCount+p.middleroadcount+p.destinationroadcount+p.x1destinationroad2count)

these fields.

so is this correct..

select *
 from SMST as p
  where  p.Source=@Source AND ( p.Destination=@Destination OR p.Destination2=@Destination) AND  [SUM[(p.sourceRoadCount+p.middleroadcount+p.destinationroadcount+p.x1destinationroad2count)]] from SMST

No, since everything in the where clause needs to be compared to something else. What you are looking for is something like

SELECT * FROM smst as p
WHERE p.Source = @Source AND (p.Destination = @Destination OR p.Destination2 = @Destination) AND
 p.sourceRoadCount+p.middleroadcount+p.destinationroadcount+p.xldestinationroad2count =
    (SELECT Min(t.sourceRoadCount+t.middleroadcount+t.destinationroadcount+t.xldestinationroad2count)
     FROM smst as t 
     WHERE t.Source = @Source AND (t.Destination=@Destination OR t.Destination2=@Destination)

You may need some GROUP BY clauses in there.

You could make it smaller by removing the inner select, specifying the fields you want (rather than *), and using GROUP BYs. Since I don't know what your table looks like, I can't really do that for you :)

Msg 156, Level 15, State 1, Procedure getdata, Line 28
Incorrect syntax near the keyword 'END'.


this is te error i got.

Instead of posting a backup to a database that we know nothing about (what database server are you using?) just post the stored procedure that is causing the problem.

This article has been dead for over six months. Start a new discussion instead.