selecting a value based on another column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

selecting a value based on another column

Author

Topic

j_dobso2
Starting Member

1 Post

Posted - 2011-10-13 : 12:22:26

Hello,

Can some one help with a query? I don't think it is that difficult, but I cannot seem to figure it out.

A statement can have multiple invoices, and dates (see the first four lines of my table). I am only interested in the 'largest' invoice number, or the 'greatest' date. I would like my return set to return each statement# only once (see line one of the desired return set)

Thanks!

Table

Statement# Inv # Date
11120100415 11120100415.1 6/04/10
11120100415 11120100415.2 5/18/11
11120100415 11120100415.3 6/28/11
11120100415 11120100415.4 6/29/11
11120100439 11120100439.1 1/05/11
11120100446 11120100446.1 7/14/11
11120100458 11120100458.1 1/27/11

Desired return set

Statement# Inv # Date
11120100415 11120100415.4 6/29/11
11120100439 11120100439.1 1/05/11
11120100446 11120100446.1 7/14/11
11120100458 11120100458.1 1/27/11

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 12:26:25

[code]
select t.[Statement#],
t.[Inv #],
t.[Date]
from table t
inner join (select [Statement#],[Inv#],max([Date]) as latest
from table
group by [Statement#],[Inv#]) t1
on t1.[Statement#] = t.[Statement#]
and t1.[Inv#]= t.[Inv#]
and t1.latest = t.[Date]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources