SQL: find distinct values for only few columns

Question

I have a table that looks like below:

ID    Address1       City   State    Zip      YearBuilt 
1    704 Grant Ave    X        Y     11111      2000
2    704 Grant Ave    X        Y     11111      2000
3    704 Grant Ave    X        Y     11111      2000
4    303 2nd St       A        B     22222      1900
5    303 2nd St       A        B     22222      1900
6    44 North ST      C        D     33333      1950

Now I want to have only those rows which have distinct Address1, City, State, and Zip. ID cannot be used as an identifier because for same address it has a different ID. I'm okay to keep the top ID.

Is there anything wrong with the results from `SELECT DISTINCT Address1, City, State, Zip FROM Table` — Andy, Jul 02 '20 at 20:21
Does this answer your question? [How to delete duplicate rows in SQL Server?](https://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server) — Eric Brandt, Jul 02 '20 at 20:23
@ParasPasrija . . . "I need the year value too" is not the question that you asked here. You should ask a new question and show the results that you want. In the meantime, the one answer is correct. — Gordon Linoff, Jul 02 '20 at 20:28

score 1 · Accepted Answer · answered Jul 02 '20 at 20:23

1

SELECT MIN(ID), Address1, City, State, Zip FROM ... GROUP BY Address1, City, State, Zip

answered Jul 02 '20 at 20:23

auntyellow

2,423
2
20
47

I also need year, but I cannot group by year – HP_17 Jul 02 '20 at 20:27
you can add this column with *Aggregate Function* like MAX, MIN or AVG. – auntyellow Jul 02 '20 at 20:31

SQL: find distinct values for only few columns

1 Answers1