|
Home > Archive > Microsoft SQL Server forum > May 2005 > Selecting Min/Max over multiple rows
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Selecting Min/Max over multiple rows
|
|
| hharry 2005-05-24, 11:23 am |
| 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!
| |
| Hugo Kornelis 2005-05-24, 8:23 pm |
| 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 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!
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 2005-05-24, 8:24 pm |
| all set, thanks hugo!
|
|
|
|
|