Find MIN but dont show it

[johnc83]

Hi all, Im struggling with a little problem which Im sure has a simple answer.

I have a SQL Select statement (quite complicated but I'll simplify for here) and I need to find the min value of the table but show a corresponding field.

Here is a snapshot of TBL_Jobs

JOBNO, ORDERNO, VALUE
123, 1, 23.00
123, 2, 50.00
124, 1, 10.00
124, 2, 15.00

I need it to show 123, 23.00 and 124, 10.00

So it needs to find the min value of the order number but not display it.

Is this possible?

Thanks

John



.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[Geoka]

One simple way is given below. Didn't checked the performance.

select b.jobno, a.value from testing a
inner join (select min(orderno) orderno, jobno from testing b group by jobno) b
on a.orderno=b.orderno and a.jobno=b.jobno

 

[PatriciaObreja]

select jobno, value from TBL_Jobs
where orderno in
(select min(orderno) from TBL_Jobs)

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[Geoka]

if min of ORDERNO for each JOBNO always same then better use code suggested by PatriciaObreja for performance.

 

[ca8msm]

Be careful if you have any null values though. An "exists" clause instead of the "in" would negate this issue though so that may be more suitable if there could be nulls.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[johnc83]

Hi Thanks for your replies.

I have got it working now but admittedly, it is a lot messier than Patricia's so I am going to try and refine it to look more that like.

I'll post back if any problems but thanks again for all replies, much appreciated.

Thanks

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express