Highly selective parent-child SQL query
When I write reports, I often times run into parent-child relationships where I only need one particular child row. And sometimes a group by simply does not cut it because max, average, sum, or some of those aggregate functions simply are not a good fit for your situation when you want some info (or the entire row) of the child that was modified last, or that has the largest amount in one column, etc.
So I use a combination the top command with an order by as seen in the query below. On the query the parents are on the SyStudent table and the children come from the AdEnroll table. There can be many AdEnrolls for a SyStudent.
For each SyStudent (parent) I’m selecting all the info of the AdEnroll (child) that was added most recently.
select stu.firstname, stu.lastname, enr.startdate, enr.gpa from systudent stu inner join adenroll enr on enr.adenrollid = ( -- get the stu's enrollment that was created most recently select top(1) enr2.AdEnrollId from AdEnroll enr2 where enr2.systudentid = stu.systudentid order by enr2.dateadded desc)
Bike dive into pond
Here’s the video I talked about on yesterday’s post.
It was taken by Daren.
I’m sorta regretting not doing it… easy to say now that I’m not there though =)
Well, and I just stumbled upon this other video… You gotta check this one out as well:
4 hour – 16 mile bike trail exploration
Yesterday we did one of the longest rides I’ve done in Salt Lake, second only to the 26 mile ride from the wasatch crest to mill creek canyon. But the 26 mile ride was mostly downhill and this one was a loop. I ended up a bit more tired after this 16 mile one; my right leg was about to cramp out.
We covered tons of terrain. Click here for a map to follow along. We started at the bottom of Clark’s trail, then started going up Jacob’s Ladder but turned right to the Alpine Perimeter trail. We wanted to get to Lambert Park, and we were so close! But we were exhausted and you want to have time to explore the trails at Lambert. So we headed back via Hog Hollow and then Down Clark’s trail again.
We did discover some very good trails, the sliding rock, and an insane jump that lands in a river.
The sliding rock (seen below), a “natural water slide”, seemed pretty bumpy to me but I saw people go down it several times so I guess it was not too bad.
Close to the sliding rock, we discovered an insane dirt jump that lands in a deep area of the river. The dirt jump had a high downhill right before it. Some dudes there had a cheap bike that they used only to fly and dive on that jump.
They gave us a demo and it looked wicked. I almost had the balls to take the dive; they offered their bike. But I still had 8 miles to ride back home and I did not want to get a bad chafe due to riding with wet underwear… or what if I got injured? … I guess it all came down to lack of balls; those dudes were hard core.
Daren got one of the dudes on video, I’ll post it once I have it.
Hit the Draper trails yesterday
I’ve gone biking on these trails almost every week this year and the last; and they never get boring. All the trails are well built and you have a couple of trails to choose from when you ride them.
Yesterday we started at the Draper temple, went up Clark’s trail, then Jacob’s Ladder, Ghost Falls, we did the two jumps on the fire road, and then went down to the bottom of the canyon on a short single-track we recently discovered.
If you’re a mt biker, you must try Jacob’s Ladder and Ghost Falls, they are awesome.
Traded website for commuter bike
Who says bartering is a thing of the past….
I just created a website for a friend (http://t3triathlon.com) in exchange for a nice commuter bike =)
I currently commute to work in my mountain bike. But I’m just 2.5 miles away. I’ll be moving to my new house on October and it is 7 miles away. So I figured a more efficient bike will definitely help out. My mt bike would work but it would suck if I get to work too sweaty, plus if commuting on a bike gets hard, I might stop doing it. So the bike is perfect, I have no excuse to stop.
Donated blood today
The need is constant and it is really not hard for us to help. Click here for some stories of the people that live because of blood donations. Visit givelife.org for a blood drive near you.
26 mile bike ride on Saturday
This past Saturday we (Cody, Shaun, and I) did an awesome ride. We started in Guardsman pass (up big cottonwood canyon) and then ended up in Mill Creek canyon.
The ride was 26 miles total. I thought it was going to be totally dead by the end of the ride. But since it was mostly downhill, I was ok. I mean I was pretty tired by the end, but I was not totally exhausted.
The ride had awesome views and varied types of trails. From narrow to wide, full speed downhill to peaceful easy downs. Most of the peaceful parts were on the Mill Creek side, the trail was full of tall pine trees, the trial was wide and it was very smooth and cushiony because of the fallen pine tree foliage.
I think this was the best ride of the year.
Carol is in Guatemala
Carol’s grandma is not doing so well health-wise so she went to Guatemala to visit her. Things are quiet without Carol around the house, but she’ll be back this Saturday.
4 pinch flats in one ride!
Last Saturday we rode the Wasatch Crest trail (one of my favorite rides) and we got new records in individual and group pinch flats! I got 2 on the same ride (record breaker #1) and Cody got another two (record breaking for him I think) making it a total of 4 pinch flats in one ride.
Strangely enough, Cody and I got our first flats and second flats at the same time. In the same rocky areas of the trail.
We were lucky that one of Cody’s pinch flats was not as severe as normal so it was still patchable. There were 3 of us riding (Darren was the third) and each had an extra tube. So we did not have to ask strangers for help.
In case you didn’t know: A pinch flat (also called snake bite) is caused when the tube is pinched between the rim and a hard, sharp object, usually a rock. When the tube is pinched, you usually get two cuts on the walls of the tube; making the holes look like a snake bite.
House update: foundation almost ready
I started an album on FB with the house progress. We’ll see the house grow from the same angle =). The framing is scheduled to begin next week.
