sql server - Is it possible to do this query without a temp table? -

if have table of data this

 tableid    author     book                 pubdate 1          1          hobbit           1923 2          1          fellowship           1925 3          2          foundation trilogy   1947 4          2          robot              1942 5          3          frankenstein         1889 6          3          frankenstein 2       1894 

is there query me following without having use temp table, table variable or cte?

 tableid    author     book                 pubdate 1          1          hobbit           1923 4          2          robot              1942 5          3          frankenstein         1889 

so want min(ranking) grouping person , ending book min(ranking) value.

ok, data gave flawed. instead of ranking column i'll have date column. need book published earliest author.

missed cte not valid (but not sure why). how subquery?

select tableid, author, book, pubdate (     select          tableid, author, book, pubdate,          rn = row_number() on          (             partition author             order pubdate         )     dbo.src -- replace real table name ) x rn = 1 order tableid; 

original:

;with x (     select          tableid, author, book, pubdate,          rn = row_number() on          (             partition author             order pubdate         )     dbo.src -- replace real table name ) select tableid, author, book, pubdate     x     rn = 1     order tableid; 

if want return multiple rows when there tie earliest book, use rank() in place of row_number(). in case of tie , want return 1 row, need add additional tie breaker columns order within over().