Selecting Min/Max over multiple rows

Hello All,

I have this table:

CREATE TABLE [dbo].[tbl_TESTING] (
[ROW_ID] [int] IDENTITY (1, 1) NOT NULL ,
[FNAME] [varchar] (50) COLLATE  SQL_Latin1_General_C
P1_CI_AS NULL ,
[LASTNAME] [varchar] (50) COLLATE  SQL_Latin1_General_C
P1_CI_AS NULL 
,
[MOVEDINYR] [int] NULL ,
[MOVEDOUTYR] [int] NULL
) ON [PRIMARY]
GO

with these records:

INSERT INTO tbl_TESTING
VALUES
(
'JAMES', 'TAYLOR', '1995', '2000'
)

INSERT INTO tbl_TESTING
VALUES
(
'JAMES', 'TAYLOR', '1994', '2005'
)

What I would like to do is be able to select FIRSTNAME, LASTNAME,
MIN(MOVEDINYR), MAX(MOVEDOUTYR) for JAMES TAYLOR e.g.

FIRSTNAME=JAMES
LASTNAME=TAYLOR
MOVEDINYR=1994
MOVEDOUTYR=2005



Some sql syntax help appreciated,

thanks in advance!

On 24 May 2005 08:33:16 -0700, hharry wrote:

>Hello All,
>
>I have this table:
>
>CREATE TABLE [dbo].[tbl_TESTING] (
>	[ROW_ID] [int] IDENTITY (1, 1) NOT NULL ,
>	[FNAME] [varchar] (50) COLLATE  SQL_Latin1_General_C
P1_CI_AS NULL ,
>	[LASTNAME] [varchar] (50) COLLATE  SQL_Latin1_General_C
P1_CI_AS NUL
L ,
>	[MOVEDINYR] [int] NULL ,
>	[MOVEDOUTYR] [int] NULL
> ) ON [PRIMARY]
>GO
>
>with these records:
>
>INSERT INTO tbl_TESTING
>VALUES
>(
>	'JAMES', 'TAYLOR', '1995', '2000'
> )
>
>INSERT INTO tbl_TESTING
>VALUES
>(
>	'JAMES', 'TAYLOR', '1994', '2005'
> )
>
>What I would like to do is be able to select FIRSTNAME, LASTNAME,
>MIN(MOVEDINYR), MAX(MOVEDOUTYR) for JAMES TAYLOR e.g.
>
>FIRSTNAME=JAMES
>LASTNAME=TAYLOR
>MOVEDINYR=1994
>MOVEDOUTYR=2005
>
>
>
>Some sql syntax help appreciated,
>
>thanks in advance!

Hi hharry,

SELECT   MIN(MovedInYr), MAX(MovedOutYr)
FROM     Testing
WHERE    FName = 'James'
AND      LastName = 'Taylor'

Or, if you want it for all people at once:

SELECT   FName, LastName, MIN(MovedInYr), MAX(MovedOutYr)
FROM     Testing
GROUP BY FName, LastName

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

all set, thanks hugo!