Return 1 record in sub-select 2

[ElectricEel]

Is it possible in a sub-select to return only 1 record if there may be a posibility of returning multiple records?
Is there a function/property similar to MaxRecords in ADO where you can specify the maximum number of records to return?

e.g.

SELECT c.casename, (SELECT rep(*only return 1 rep) FROM Reps r WHERE r.casenumber = @casenumber) as rep
FROM Cases c
WHERE c.casenumber = @casenumber

Aloha
Neil

 

[balves]

There's TOP in SQL V7 and beyond.

It works better if Reps is a derived table:

SELECT c.casename, r.rep
FROM Cases c,
(SELECT Top 1 Rep, casenumber FROM Reps WHERE casenumber = @casenumber) r
INNER JOIN ON c.casenumber = r.casenumber
WHERE c.casenumber = @casenumber

 

[arrowhouse]

SELECT TOP 1 ....


If you have one case name per case number I think you could replace the subselect with

SELECT TOP 1 c.casename, r.rep
FROM Cases c
INNER JOIN
/*
unless you have cases with out reps inwhich case left outer
*/
Reps r
ON
r.casenumber = c.casenumber
AND
c.casenumber = @casenumber

 

[ElectricEel]

balves & arrowhouse

TOP! just what I was lookin' for...
Thanks

Neil