SQLTeam.com | Weblogs | Forums

Query to filter based on criteria


#1

Hi
Totally new to SQL and do not really have a clue what I am doing, so apologies if this doesn't make sense.

I have a SQL table of approx. 10,000 records, with three fields similar below

ID Level Date
123 1 01/02/2015
123 2 03/01/2015
123 7 03/07/2015
456 0 27/09/2014
456 2 30/01/2015
78749 2 01/12/2014
78749 3 03/03/2015

What I need to do is to be able to extract per ID, the first 'Level' based on the earliest Date and also the Highest Level relating to the same ID. So for ID 123, the First Level will be "2" and the Highest would be "7". Ideally the output should be like this:

ID     First Level   Highest Level
123       2               7

If anyone can point me in the direction I need to go with this, that would be great

TIA


#2
SELECT
	a.id,
	b.[First Level],
	MAX(a.Level) AS [Highest Level]
FROM
	Tbl a
	CROSS APPLY
	(
		SELECT TOP (1) Level AS [First Level]
		FROM Tbl b
		WHERE b.Id = a.Id
		ORDER BY b.Date ASC
	) AS b
GROUP BY
	a.id,
	b.[First Level]

#3

or:

-- *** Test Date in Consumable Format *** ---
-- Please provide in future
CREATE TABLE #t
(
	ID int NOT NULL
	,Level int NOT NULL
	,Date date
);
INSERT INTO #t
VALUES (123, 1, '20150201')
	,(123, 2, '20150103')
	,(123, 7, '20150703')
	,(456, 0, '20140927')
	,(456, 2, '20150130')
	,(78749, 2, '20141201')
	,(78749, 3, '20150303');
-- *** End Test Data ***

-- Option 1
WITH MinMax
AS
(
	SELECT ID
		,MIN(Date) AS MinDate
		,MAX(Date) AS MaxDate
	FROM #t
	GROUP BY ID
)
SELECT M.ID, I.Level, A.Level
FROM MinMax M
	JOIN #t I
		ON M.ID = I.ID
			AND M.MinDate = I.Date
	JOIN #t A
		ON M.ID = A.ID
			AND M.MaxDate = A.Date;

-- Option 2 (Might be more efficient)
WITH MinMax
AS
(
	SELECT *
		,MIN(Date) OVER (PARTITION BY ID) AS MinDate
		,MAX(Date) OVER (PARTITION BY ID) AS MaxDate
	FROM #t
)
SELECT ID
	,MIN(CASE WHEN Date = MinDate THEN Level END) AS FirstLevel
	,MIN(CASE WHEN Date = MaxDate THEN Level END) AS LastLevel
FROM MinMax
GROUP BY ID;