473,326 Members | 2,048 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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_CP1_CI_AS NULL ,
[LASTNAME] [varchar] (50) COLLATE SQL_Latin1_General_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!

Jul 23 '05 #1
2 2857
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_CP1_CI_AS NULL ,
[LASTNAME] [varchar] (50) COLLATE SQL_Latin1_General_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!


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)
Jul 23 '05 #2
all set, thanks hugo!

Jul 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Robert | last post by:
Hi All, I'm trying to solve this for a few days now and I just can't figure it out... I have three tables set up, I'll simplify them for this question: Table 1: HOTELS Columns: HOTEL_ID,...
0
by: David | last post by:
I'm creating a C# (.NET BETA2) Windows application (NOT a Web application, a Windows application), and I would like to select multiple rows inside of a DataGridView. Is this possible and if so...
1
by: Jay | last post by:
Hi All, My users are complaining about the page refreshing when they are selecting multiple rows in a datagrid. Has anyone tried to manage this using javascript? I tried smartnavigation but that...
0
by: PontiMax | last post by:
Hi, I would like to enhance the DataGrid by adding scripting functionality that allows the selection of multiple rows or columns via drag-and-drop. Can anyone provide some examples or...
1
by: sneha123 | last post by:
There will be some 20 questions and for each question there will be 4 choices.what i want to do is to select multiple answers by clicking the checkbox. i m using asp.net,vb.net pls help me we...
1
by: Bob Loveshade | last post by:
I am looking for an example that shows how to select and highlight multiple rows in a DataGrid. My DataGrid is part of a Web User Control which is contained in an ASPX page. I haven't been...
1
by: Andy | last post by:
Hi Gang I have a simple single table that has many duplicate rows in them. The distinctrows only works on more than one table. Is there a quick and easy way to select only the distict rows...
1
by: nolanson | last post by:
I'm raw with MS Access and have been stumped for hours and hours on this. I have a table that has ticket rows (like an invoice - identified by a unique service date and call number) and remit rows...
2
by: ravitunk | last post by:
hi..i have a datagridview in my windows application using C#......i want to select mutiple cells(by pressing shift key) or select mutiple columns or multiple rows.....if this happens then i should...
0
by: bplthebest | last post by:
Hi I have have functionality like Selecting multiple rows (without using check boxes) in Grid view by Ctrl+click (IE) and shift+click(safari and Firefox). And edit the selected rows in a panel...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.