Eliminating duplicate values based on only one column of the table

by

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

More Related Contents:

  1. Difference between JOIN and INNER JOIN
  2. How can I delete using INNER JOIN with SQL Server?
  3. Is having an ‘OR’ in an INNER JOIN condition a bad idea?
  4. WHERE Clause vs ON when using JOIN
  5. Produce DISTINCT values in STRING_AGG
  6. Row Offset in SQL Server
  7. Reset identity seed after deleting records in SQL Server
  8. How to avoid the “divide by zero” error in SQL?
  9. SQL Server recursive self join
  10. Multiple INSERT statements vs. single INSERT with multiple VALUES
  11. How to simulate UNPIVOT in Access?
  12. Real life example, when to use OUTER / CROSS APPLY in SQL
  13. How to get script of SQL Server data? [duplicate]
  14. Better techniques for trimming leading zeros in SQL Server?
  15. Add unique constraint to combination of two columns
  16. What does a transaction around a single statement do?
  17. SQL GROUP BY CASE statement with aggregate function
  18. How can I include null values in a MIN or MAX?
  19. separate comma separated values and store in table in sql server
  20. Query a database based on result of query from another database
  21. How to delete the top 1000 rows from a table using Sql Server 2008?
  22. Get the last day of the month in SQL
  23. INSERT VALUES WHERE NOT EXISTS
  24. Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
  25. Inner Joining three tables
  26. SQL take just the numeric values from a varchar
  27. How do I change db schema to dbo
  28. Is there a severe performance hit for using Foreign Keys in SQL Server?
  29. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  30. How to insert into a table with just one IDENTITY column?

Leave a Comment