Hello all,
I am trying to write an procedure that will return the topmost row of
each author id that is in the table. This is what I have so far :

select  * from PreviousBills where [BatchDate] >= @date or [Date] >=
@date order by [Date] DESC, [BatchDate] DESC

this will return an ordered set of records but I don't know how to get
it to just return the topmost of each author id. the table looks like
so

AuthorID | Date | Statement | BatchDate | ID


there can be multiple of the same authorID's in the table I just want
the one that is the most reccent date.

I'm sure its something stupid but I can't figure out how to phrase it
right to google it.

Thanks

jjbu***@gmail.com wrote:

Something like this:

select PB1.* from PreviousBills PB1 where PB1.BatchDate >= @date or
PB1.date >= @date and PB1.date = (select top 1 PB2.date from
PreviousBills PB2 where PB2.AuthorID = PB1.AuthorID
and (PB2.BatchDate >= @date or PB2.date >= @date)
order by PB2.date desc)

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.  It is very hard to debug code when you do not let us
see it.

You also need to learn not use reserved words like DATE and vague
meaningless names like "id" or "statement" for columns.  There is also
no such concept as "topmost" in RDBMS because tables have no ordering.
Did you mean most recent date?

--CELKO-- wrote:

'there can be multiple of the same authorID's in the table I just want
the one that is the most reccent date.'

That's what the man said :).

Sum Total Hours and Minutes in a report
Aggregate Function Error
unique variable per connection
RFC: EXISTS (SELECT ... FROM) optional?
use subquery result in Selet statement