Eliminating duplicate values based on only one column of the table

sql sql-server distinct inner-join duplicate-removal
Ned picture Ned · Jul 7, 2013 · Viewed 101.4k times · Source

My query:

SELECT sites.siteName, sites.siteIP, history.date
FROM sites INNER JOIN
     history ON sites.siteName = history.siteName
ORDER BY siteName,date

First part of the output:

enter image description here

How can I remove the duplicates in siteName column? I want to leave only the updated one based on date column.

In the example output above, I need the rows 1, 3, 6, 10

Answer

Gordon Linoff picture Gordon Linoff · Jul 7, 2013

This is where the window function row_number() comes in handy:

SELECT s.siteName, s.siteIP, h.date
FROM sites s INNER JOIN
     (select h.*, row_number() over (partition by siteName order by date desc) as seqnum
      from history h
     ) h
    ON s.siteName = h.siteName and seqnum = 1
ORDER BY s.siteName, h.date

Related questions

SELECT DISTINCT on one column

Using SQL Server, I have... ID SKU PRODUCT ======================= 1 FOO-23 Orange 2 BAR-23 Orange 3 FOO-24 Apple 4 FOO-25 Orange I want 1 FOO-23 Orange 3 FOO-24 Apple This query isn't getting me there. How can I SELECT DISTINCT on just one column? SELECT [ID],[SKU],[…

Read More
how to select rows based on distinct values of A COLUMN only

I need to query a table in order to return rows, but I am not able to query the table correctly. Here is my table view: Id MailId EmailAddress Name 1 1 [email protected] Mr. A 2 1 [email protected] Mr. B 3 1 …

Read More
SQL - select distinct only on one column

I have searched far and wide for an answer to this problem. I'm using a Microsoft SQL Server, suppose I have a table that looks like this: +--------+---------+-------------+-------------+ | ID | NUMBER | COUNTRY | LANG | +--------+---------+-------------+-------------+ | 1 | 3968 | UK | …

Read More