Select from results of query?
15,832
Solution 1
;with C as
(
select W.Member,
W.Weight_kg,
row_number() over(partition by W.Member order by S.datetime desc) as rnLast,
row_number() over(partition by W.Member order by S.datetime asc) as rnFirst
from Weighings as W
inner join Sessions as S
on S.sessionguid = W.Session and
S.DateTime >= '20110101'
)
select CF.Member,
CF.Weight_kg as FirstWeight,
CL.Weight_kg as LastWeigth
from C as CF
inner join C as CL
on CF.Member = CL.Member
where CF.rnFirst = 1 and
CL.rnLast = 1
Try here: https://data.stackexchange.com/stackoverflow/q/118518/
Solution 2
You can use the RANK..OVER stmt (works only on SQL 2k5+)
select fw.Member, st.Weight, en.Weight
from
(
select Member, Weight, RANK() OVER(PARTITION BY Member ORDER BY Weight) rnk
from Weighings
) st
inner join
(
select Member, Weight, RANK() OVER(PARTITION BY Member ORDER BY WeightDESC) rnk
from Weighings
) en on en.Member= st.Member and st.rnk = 1 and en.rnk = 1
Comments
-
Myles Gray almost 2 years
I have a query like this:
SELECT Weighings.Member, MIN(Sessions.DateTime) AS FirstDate, MAX(Sessions.DateTime) AS LastDate FROM Weighings AS Weighings INNER JOIN Sessions ON Sessions.SessionGUID = Weighings.Session WHERE (Sessions.DateTime >= '01/01/2011') GROUP BY Weighings.Member ORDER BY Weighings.Member
It returns this:
Member | FirstDate | LastDate Blah | 01/01/11 | 06/07/11 Blah2 | 02/03/11 | 05/07/11
I need to get the value of a cell
Weight_kg
in tableWeighings
for the returned valuesFirstDate
andLastDate
to give results like so:Member | FirstWeight | LastWeight Blah | 150Kg | 60KG Blah2 | 70Kg | 72KG
I have tried all combinations of things but just can't work it out, any ideas?
EDIT
Tables:
Sessions ______________________ SessionGUID | DateTime ---------------------- 12432524325 | 01/01/11 12432524324 | 01/08/11 12432524323 | 01/15/11 34257473563 | 03/05/11 43634574545 | 06/07/11 Weighings _____________________________________ Member | Session | Weight_kg ------------------------------------- vffd8fdg87f | 12432524325 | 150 vffd8fdg87f | 12432524324 | 120 vffd8fdg87f | 12432524323 | 110 ddffv89sdv8 | 34257473563 | 124 32878vfdsv8 | 43634574545 | 75
-
Ash Burlaczenko over 12 yearsYour not creating a
Temporary
table here.