Actually there is no such thing as "the first 10 rows". If I understand you right, you have two alternatives:

USE PUBS
GO
SET ROWCOUNT 10
SELECT au_lname FROM authors ORDER BY au_lname
SET ROWCOUNT 0

--or
SELECT TOP 10 au_lname FROM authors ORDER BY au_lname

Okay, what about:

set nocount on
create table hans
(
 name varchar(10)
 , date datetime default getdate()
)

insert into hans (name, date) values('Arley','20040302')
insert into hans (name, date) values('Arley','20040310')
insert into hans (name, date) values('Arley','20040312')
insert into hans (name, date) values('Arley','20040301')
insert into hans (name, date) values('Ben','20040310')
insert into hans (name, date) values('Ben','20040317')

select
 t1.name
 , t1.date
from
 hans t1
where
 t1.date in
(
 select top 2 with ties
  t2.date
 from
  hans t2
 where
  t2.name = t1.name
 order by
  t2.date desc
)
order by
 t1.name
 , t1.date desc
drop table hans
set nocount off

name       date                                                  
---------- ------------------------------------------------------
Arley      2004-03-12 00:00:00.000
Arley      2004-03-10 00:00:00.000
Ben        2004-03-17 00:00:00.000
Ben        2004-03-10 00:00:00.000

Oh, and may I add that you shouldn't use use words as name and date as identifiers for column?

No need to worry!

Have a play with it and you'll see

set nocount on
create table hans
(
 name varchar(10)
 , date datetime default getdate()
)

insert into hans (name, date) values('Arley','20040302')
insert into hans (name, date) values('Arley','20040310')
insert into hans (name, date) values('Arley','20040312')
insert into hans (name, date) values('Arley','20040301')
insert into hans (name, date) values('Frank','20040310')
insert into hans (name, date) values('Ben','20040317')

select
 t1.name
 , t1.date
from
 hans t1
where
 t1.date in
(
 select top 2 with ties
  t2.date
 from
  hans t2
 where
  t2.name = t1.name
 order by
  t2.date desc
)
order by
 t1.name
 , t1.date desc
drop table hans
set nocount off

name       date                                                  
---------- ------------------------------------------------------
Arley      2004-03-12 00:00:00.000
Arley      2004-03-10 00:00:00.000
Ben        2004-03-17 00:00:00.000
Frank      2004-03-10 00:00:00.000

This should work on as many distinct names as you have in your table.

how does the "with ties" work in this situation?  I've yet to ever find a need for that feature, but I am wondering if perhaps I've just found more difficult ways of acheiving the same solution.

Cheers,

Quentin

The WITH TIES is not really needed in this situation, I think, because of the combination of ...IN... TOP 2 ... WITH TIES. If you have two or more rows with the same date and this date falls within the TOP 2 it will be handled by the IN condition anyway. Here's a maybe better example of what WITH TIES does:

SET NOCOUNT ON
DECLARE @TTT TABLE
(
     THE_ID INT NOT NULL
     , Name varchar(50)
)
INSERT INTO @TTT VALUES(1,'A');
INSERT INTO @TTT VALUES(2,'BB');
INSERT INTO @TTT VALUES(2,'B');
INSERT INTO @TTT VALUES(3,'C');
INSERT INTO @TTT VALUES(3,'D');
INSERT INTO @TTT VALUES(3,'DD');
INSERT INTO @TTT VALUES(3,'DDD');
INSERT INTO @TTT VALUES(3,'DDDD');
INSERT INTO @TTT VALUES(4,'E');
INSERT INTO @TTT VALUES(5,'F');
INSERT INTO @TTT VALUES(6,'G');
SET NOCOUNT OFF

SELECT TOP 4
     THE_ID
FROM
      @TTT
ORDER BY
     1;

SELECT TOP 4 WITH TIES
     THE_ID
FROM
      @TTT
ORDER BY
     1;

SELECT DISTINCT TOP 4
     THE_ID
FROM
      @TTT
ORDER BY
1;

Got this from a posting here by Len Esterhuyse.