0
DECLARE @Test TABLE (id INT,smo NVARCHAR(100),grad NVARCHAR(100),lst int)

INSERT INTO @Test
SELECT 11,'Ali','G',1
UNION ALL
SELECT 11,'Ali','C',1
UNION ALL
SELECT 11,'Ali','F',0
UNION ALL
SELECT 12,'Veli','C',1
UNION ALL
SELECT 12,'Veli','G',1


SELECT t.id, t.smo, t.grad,c.* FROM @Test t
CROSS APPLY(
SELECT  MIN(lst) glst FROM @Test t1
WHERE t1.id=t.id
GROUP BY id, smo
) c

The @Test table is actually my result from another query.

The thing that i want is, recieving the min value from 'lst' column for each 'smo' value.

With cross apply i can achieve what i want, when it is a table.

How can i solve it when i am in a condition like that.

SELECT * FROM (
SELECT * FROM @Test 

) t

enter image description here INPUT

enter image description here Expected OUTPUT

Thank you in advance for your help...

Burak
  • 45
  • 4
  • Your answer is already working so I am trying to understand what you need? If it's the result of a function, can't you just wrap the function in an outer apply and do the same thing you are doing above? – Kevin Cook Jul 15 '14 at 12:40
  • It is becoming like SELECT t.id, t.smo, t.grad, c.*FROM ( SELECT * FROM @Test ) t CROSS APPLY ( SELECT MIN(t1.lst) glst FROM t t1 WHERE t1.id = t.id GROUP BY t1.id, t1.smo ) c So it doesnt make sense. The query i get the result is big a little and i dont want to repeat it. I consider of using a temporary table to hold the id and MIN(lst) than cross apply it but i hope to solve in a nicer way. – Burak Jul 15 '14 at 13:20
  • You could try using a [CTE](http://msdn.microsoft.com/en-us/library/ms175972.aspx) if your goal is to cross apply your result set to itself. – stubaker Jul 17 '14 at 21:54

0 Answers0