Select distinct

which one of the rows do you want to take, per template_id?

Why these particular rows?  What is significant about location id 1301 and 1322?  Is it because these appear first in the list or because these are the lowest value locations for each template_id?

> which one of the rows do you want to take, per template_id?

>Why these particular rows?  What is significant about location id 1301 and 1322?  Is it because these >appear first in the list or because these are the lowest value locations for each template_id?

Because they appear the first. I just want one copy for each template_id

Have you tried the query I posted? Btw, is location_id? If its not, try this

SELECT DISTINCT file_id,location_id,entity_id,template_id
FROM file_location f
WHERE entity_id=1025 AND
      file_id = (SELECT TOP 1 file_id FROM file_location WHERE entity_id=f.entity_id AND template_id=f.template_id ORDER BY file_id,location_id) AND
      location_id = (SELECT TOP 1 location_id FROM file_location WHERE entity_id=f.entity_id AND template_id=f.template_id ORDER BY file_id,location_id)

                                              

1:
2:
3:
4:
5:

Yes I tried it but it's not producing the result I want, let me explain more here

file_id       location_id      entity_id      template_id
100333    1255               1028            331
100333    1301               1025            331
100333    1322               1025            332
100333    1325               1025            332     <-- for the same file_id, i just want one row per template_id
100333    1326               1025            332     <-- for the same file_id, i just want one row per template_id
100335    1388               1028            331
100334    1328               1025            332     <--- I want this because the file_id is different

My desired output:
100333    1301               1025            331
100333    1322               1025            332
100334    1328               1025            332    

And the table is actually generated by 2 different tables, joined by file_id. The location_id the assoc_image table is just for quick reference, although it's value should be the same as the corresponding entry in the file_location table. See below:

file_location:
+ file_id
+ location_id
+ entity_id

assoc_image
+ file_id
+ location_id
+ template_id

Thanks,

Stephen

try

SELECT *
FROM file_location f
WHERE entity_id=1025 AND
      location_id = (SELECT MIN(location_id) FROM file_location WHERE entity_id=f.entity_id AND template_id=f.template_id AND file_id=f.file_id)

                                              

1:
2:
3:
4:

sorry, ignore my last post. try

SELECT f.file_id, f.location_id, f.entity_id, MIN(a.template_id) template_id
FROM file_location f INNER JOIN assoc_image a ON f.file_id=a.file_id AND f.location_id=a.location_id
WHERE f.entity_id=1025
GROUP BY f.file_id, f.location_id, f.entity_id

                                              

1:
2:
3:
4:

Scrap my previous comment, just noticed you answered the question earlier - Because it appears first.

The SQL required is:

select file_id, min(location_id) location_id, entity_id, template_id
from file_location
where entity_id = 1025
group by file_id, entity_id, template_id

                                              

1:
2:
3:
4:

SELECT file_id, location_id, entity_id, template_id
  FROM (SELECT file_id, location_id, entity_id, template_id,
               ROW_NUMBER () OVER (PARTITION BY template_id ORDER BY location_id)
                                                                            r
          FROM file_location
         WHERE entity_id = 1025)
 WHERE r = 1

ee_rlee,

Your last SQL statement works, but how do I find out the number of rows it returns?

Thanks,
Stephen