looking for help in sql-server query

Question

hi

i have this table:

MEN
===

barcode | CustNum | Tdate
-------------------------------
123      |  1            | 01.01.2012
123      |  1            | 04.01.2012
123      |  1            | 06.01.2012
444      |  1            | 01.01.2012
444      |  1            | 05.01.2012
555      |  1            | 01.01.2012
555      |  2            | 02.01.2012
555      |  2            | 01.01.2012

and i need to show the max date of all barcode and CustNum like this:

barcode | CustNum | Tdate
-------------------------------
123      |  1            | 06.01.2012
444      |  1            | 05.01.2012
555      |  1            | 01.01.2012
555      |  2            | 02.01.2012

thanks in advance

Answer 1

SELECT * FROM

(

SELECT * ,ROW_NUMBER() OVER (PARTITION BY barcode,CustNum ORDER BY tdate DESC) rn

FROM tbl

) AS Der WHERE rn=1

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

A simple group by clause should server you well

SELECT barcode, CustNum, MAX(Tdate)
FROM MEN
GROUP BY barcode, CustNum

Optionally you can also add an ORDER BY barcode, CustNum clause to order the results.

Regards,
Alvaro

Answer 3

A simple group by clause should server you well

SELECT barcode, CustNum, MAX(Tdate)
FROM MEN
GROUP BY barcode, CustNum

Optionally you can also add an ORDER BY barcode, CustNum clause to order the results.

Regards,
Alvaro

As per the expected results for BarCode 555, it seems that it is ok to return one row each for CustNum 1 and 2. In that case Alvaro's solution is sufficient.

If you find out later that even the BarCode 555 should have had only one row in the expected results i.e. CustNum 2, then Uri's query would do that.

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | (http://aalamrangi.wordpress.com)

Answer 4

thanks !!! its works !!!