Unanswered: Retrieve ONLY First Picture Record For Each Customer
I need a specific SQL solution to use for an image gallery that would display the first picture for each customer for that specific job type. Here is some condensed sample data for each of the two tables.
jobNo refName refProj
1 Johnson Concrete
2 Blake Concrete
3 Jones Landscaping
picID picDescrip refPicLink (which is a FK linked to jobNo)
1 Sidewalk 1
2 Driveway 1
3 Wall 1
4 Basement 2
5 Wall 2
6 Bushes 3
So on my Concrete page, I would ONLY be retrieving the records with picID 1 and 4 and for the Landscaping page I would be retrieving picID 6.
I have tried several combination of using DISTINCT and also TOP with my SELECT statements but I'm guessing there is a much more efficient way to handle this.
SELECT rp.*, r.refName, r.refProj
FROM ReferencePicture rp
JOIN Reference r ON r.jobNo = rp.refPicLink
WHERE rp.picID = (SELECT min(rp2.picID)
FROM ReferencePicture rp2
WHERE rp2.refPicLink = rp.refPicPicLink)