retrieve rows and join based on MIN & Max

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

retrieve rows and join based on MIN & Max

Author

Topic

David_G
Starting Member

10 Posts

Posted - 2009-09-25 : 01:11:35

I need to retrieve rows and join based on MIN(User_Sequence) and Max(User_Sequence) I woul like to have the columns retrieved as per example below. I would be forever greatful if someone could help.

I have a table as follows...
CREATE TABLE [dbo].[Items](
[PK] [bigint] IDENTITY(1,1) NOT NULL,
[User_Item] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[User_Type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('EACH'),
[User_Sequence] [int] NOT NULL DEFAULT ((1)),
[User1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Date_Time] [datetime] NULL DEFAULT (getdate()),
CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED (
[PK] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO Items
(User_Item ,User_Type,User_Sequence,User1,Date_Time)
VALUES ('AAA','EACH',1,'ABC111','')

INSERT INTO Items
(User_Item ,User_Type,User_Sequence,User1,Date_Time)
VALUES('AAA','EACH',2,'ABC222','')

INSERT INTO Items
(User_Item ,User_Type,User_Sequence,User1,Date_Time)
VALUES ('AAA','EACH',3,'ABC333','')

INSERT INTO Items
(User_Item ,User_Type,User_Sequence,User1,Date_Time)
VALUES ('AAA','UNIT',1,'ABC111','')

INSERT INTO Items
(User_Item ,User_Type,User_Sequence,User1,Date_Time)
VALUES ('BBB','EACH',1,'ABC111','')

INSERT INTO Items
(User_Item ,User_Type,User_Sequence,User1,Date_Time)
VALUES('BBB','EACH',2,'ABC222','')

Select * from Items

4 AAA EACH 1 ABC111 2009-08-26 12:54:56.000
5 AAA EACH 2 ABC222 2009-08-26 12:55:56.000
6 AAA EACH 3 ABC333 2009-08-26 12:56:56.000
7 AAA UNIT 1 ABC111 2009-08-26 12:57:56.000
8 BBB EACH 1 ABC111 2009-08-26 12:58:56.000
9 BBB EACH 2 ABC222 2009-08-26 12:54:56.000

This is what I would like to achieve....

Select and join the rows with MIN(User_Sequence) and Max(User_Sequence)

Group by User_Item

Was Is Was Is Was Is Was Is Was Date Is Date
--- --- ---- ---- - - ------ ------ ---------------------- ----------------------
AAA AAA EACH EACH 1 3 ABC111 ABC333 2009-08-26 12:54:56.000 2009-08-26 12:56:56.000
AAA AAA UNIT UNIT 1 1 ABC111 ABC111 2009-08-26 12:57:56.000 2009-08-26 12:57:56.000
BBB BBB EACH EACH 1 2 ABC111 ABC222 2009-08-26 12:58:56.000 2009-08-26 12:58:56.000

Hope someone can help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-25 : 14:51:00

[code]SELECT User_Item,User_Type,User_Item,User_Type,
MAX(CASE WHEN FRowNo=1 THEN User_Sequence ELSE NULL END),
MAX(CASE WHEN BRowNo=1 THEN User_Sequence ELSE NULL END),
MAX(CASE WHEN FRowNo=1 THEN User1 ELSE NULL END),
MAX(CASE WHEN BRowNo=1 THEN User1 ELSE NULL END),
MAX(CASE WHEN FRowNo=1 THEN Date_Time ELSE NULL END),
MAX(CASE WHEN BRowNo=1 THEN Date_Time ELSE NULL END)
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY User_Item ,User_Type ORDER BY User_Sequence) AS FRowNo,
ROW_NUMBER() OVER (PARTITION BY User_Item ,User_Type ORDER BY User_Sequence DESC) AS BRowNo,*
FROM YourTable
)t
GROUP BY User_Item,User_Type
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources