SQL query to sort the ID as ASC and Zip must not dup

Question

I have an SQL statement like this:

SELECT         TOP 100 id, 
               lastname, 
               firstname, 
               address1, 
               city, 
               state, 
               zip 
FROM   leads 
WHERE  id > 100 
ORDER  BY id ASC

Now I want the ZIP to be different (not dup) for 100 result with 1 query statement like that.

SQLOLEDB, sory my mistake. I try use DISTINCT but dont know how — learnscript, Apr 08 '15 at 03:18
I tried many type but not success "SELECT DISTINCT * from (select DISTINCT Zip,* from (select * FROM LEADS order by ID asc))" — learnscript, Apr 08 '15 at 03:57

score 1 · Accepted Answer · answered Apr 10 '15 at 06:21

SELECT         TOP 100 l.id, 
               l.lastname, 
               l.firstname, 
               l.address1, 
               l.city, 
               l.state, 
               l.zip 
FROM   leads l
WHERE   l.id = (select MIN (id) FROM leads l2 where l2.zip=l.zip)
ORDER  BY l.id ASC

SQL query to sort the ID as ASC and Zip must not dup

1 Answers1