Max Function

USE [Infosys_Connie]
GO
/****** Object:  Table [dbo].[KNA1]    Script Date: 12/21/2009 14:52:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[KNA1](
 [UID] [int] NOT NULL,
 [SALARY] [int] NULL,
 [DEPARMENT] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

/*
INSERT INTO [KNA1] VALUES (1,100,'RETEL');
INSERT INTO [KNA1] VALUES (2,200,'RETEL');
INSERT INTO [KNA1] VALUES (3,300,'RETEL');
INSERT INTO [KNA1] VALUES (4,100,'EUS');
INSERT INTO [KNA1] VALUES (5,400,'EUS');
INSERT INTO [KNA1] VALUES (6,100,'EDS');
INSERT INTO [KNA1] VALUES (7,100,'EDS');
INSERT INTO [KNA1] VALUES (8,50,'EDS');
*/

SELECT * FROM KNA1 ORDER BY DEPARMENT
GO
--SELECT DEPARMENT,MAX(SALARY) AS M_SALARY FROM KNA1 GROUP BY DEPARMENT ORDER BY DEPARMENT
--GO

SELECT A.UID,A.DEPARMENT,B.M_SALARY FROM KNA1 A
INNER JOIN
(SELECT DEPARMENT,MAX(SALARY) AS M_SALARY FROM KNA1 GROUP BY DEPARMENT) AS B
ON A.DEPARMENT=B.DEPARMENT AND A.SALARY=B.M_SALARY
GO

USE AdventureWorks;
GO
select
c.* from
(
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity, RANK() OVER (PARTITION BY i.LocationID order by i.Quantity) as RANK
FROM Production.ProductInventory i JOIN Production.Product p
ON i.ProductID = p.ProductID
ORDER BY p.Name
) as c
GO