quote:

---

SELECT
distinct
TimeGenerated,
EXTRACT_TOKEN(Strings,5,'|') AS User,
EXTRACT_TOKEN(Strings,18,'|') AS ClientAddress,
EventID
FROM \\MYSERVER\%SOURCE%
WHERE EventID='4624' AND TimeGenerated > '2012-09-04 23:59:00' and user not like '%$%' and user not like '%ANONYMOUS%' and user = 'testus'
--ORDER BY TimeGenerated ASC

---

;WITH cte AS
(
	SELECT *,
		ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY timegeneraged DESC) AS RN
	FROM
		YourTable
)
SELECT * FROM cte WHERE RN = 1;

quote:

---

Originally posted by sunitabeck

One way is to use the row_number function. In the query below, what you need to change are indicated in RED. The partition by clause should have the columns that you want to consider as distinct. The order by clause should specify how to order it so that you will get the first one in that order by clause.

;WITH cte AS
(
	SELECT *,
		ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY timegeneraged DESC) AS RN
	FROM
		YourTable
)
SELECT * FROM cte WHERE RN = 1;

---

quote:

---

Originally posted by squirrelsuccess

Thanks for the solution. It didn't work for me but thats because the function isn't available on SQL 2000. We are moving the DB to SQL 2008 soon anyway so I know it will work there. I did try the syntax with some sample data on a SQL 2005 server which we had lying around and the syntax worked fine.

Thanks for your assistance.

---

SELECT t.*
FROM table t
INNER JOIN (SELECT col1,col2,col3,MAX(datefield) AS latest
            FROM table
            GROUP BY col1,col2,col3
           )t1
ON  t.col1 = t1.col1
AND t.col2 = t1.col2
AND t.col3 = t1.col3
AND t.datefield = t1.latest