Your question, your audience. Choose who sees your identity—and your question—with question security.
create table #table
(
ZIP VARCHAR(10),
REP VARCHAR(10),
DATE DATETIME
)
INSERT INTO #table
SELECT '12345', 'AAAA', '03/15/2011' UNION ALL
SELECT '12345', 'ZZZZ', '03/01/2011' UNION ALL
SELECT '12346', 'AAAA', '03/12/2011' UNION ALL
SELECT '12346', 'ZZZZ', '03/01/2011'
SELECT * FROM #TABLE
-- Accepted Solution
SELECT * FROM #TABLE
select MAX(ZIP) ZIP, MAX(REP) REP, MAX(DATE) DATE
from #table
group by ZIP
order by DATE DESC
-- My Query
SELECT * FROM #TABLE
select REP,ZIP,DATE from
(
select *, row_number() over (partition by zip order by date DESC) rowno
from #table
) a
where a.rowno = 1
Drop Table #TABLE
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
select distinct zip
from table
order by zip