Selecting Min/Max over multiple rows

[hharry]

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_Gene ral_CP1_CI_AS NULL ,
[LASTNAME] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_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!

[Hugo Kornelis]

Re: Selecting Min/Max over multiple rows

On 24 May 2005 08:33:16 -0700, hharry wrote:
[color=blue]
>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_Gene ral_CP1_CI_AS NULL ,
> [LASTNAME] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_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=JAME S
>LASTNAME=TAYLO R
>MOVEDINYR=19 94
>MOVEDOUTYR=200 5
>
>
>
>Some sql syntax help appreciated,
>
>thanks in advance![/color]

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)

[hharry]

Re: Selecting Min/Max over multiple rows

all set, thanks hugo!