May 24, 2010 at 1:15 pm
Here's the query I have so far. I don't want to specify a particular my_app_id. I want to do this for all unique my_app_id's in the my_log_table. As the description implies, the my_log_table may have many records for a particular my_app_id, but I just want the most recent one. This is found by looking at the date in the last_update field.
select top 1 * from my_log_table
where my_app_id=10
order by last_update desc
This query does what I want, but only for the my_app_id with a value of 10.
I'd like to throw this in a stored procedure. I was thinking of first creating a temp table to store all the unique my_app_id's. Then join that on the my_log_table, but I'm stumped how to do this for all unique my_app_id's in the my_log_table.
As an added bonus, it would be nice if it would only return those records where the latest status is error. So something like this: where my_log_table.status='error'. Or if that's too hard, at least sort it by status.
May 24, 2010 at 1:44 pm
Could you provide a table definition along with the columns that need to be returned in the query (rather than just *)?
This would be extremely helpful.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 24, 2010 at 1:44 pm
Something like the following code snippet?
I used a CTE to get the latest update-time per app_id and joined that back to the main table. The result is filtered to show only error rows.
;
WITH cte AS
(
SELECT my_app_id, MAX(last_update) AS max_last_upd
FROM my_log_table
GROUP BY my_app_id
)
SELECT my_log_table.*
FROM my_log_table
INNER JOIN cte
ON my_log_table.my_app_id=cte.my_app_id
AND my_log_table.last_update=cte.max_last_upd
WHERE my_log_table.status='error'
May 24, 2010 at 3:15 pm
Nice! Thanks for introducing me to CTE's 🙂 And I don't know why I didn't think of doing a MAX on the date, but that would have made things 10 times easier. I couldn't figure out how to do a group by with a TOP function. Anyway, your solution worked just great.
One thing I didn't mention that may have changed things slightly, is the my_log_table has an id field, but as far as I can tell, selecting that field in the group by query wouldn't have worked. I'd feel a little better if I could join the 2 tables on that id field instead of the max_last_upd and my_app_id fields, but those 2 combined should return a unique result anyway.
So is there a way to award you points or anything on this forum?
This is off topic, but it would be nice to convert this to nhibernate (like hql) instead of using a sproc.
May 24, 2010 at 3:28 pm
swingnchad1 (5/24/2010)
Nice! Thanks for introducing me to CTE's 🙂 And I don't know why I didn't think of doing a MAX on the date, but that would have made things 10 times easier. I couldn't figure out how to do a group by with a TOP function. Anyway, your solution worked just great.One thing I didn't mention that may have changed things slightly, is the my_log_table has an id field, but as far as I can tell, selecting that field in the group by query wouldn't have worked. I'd feel a little better if I could join the 2 tables on that id field instead of the max_last_upd and my_app_id fields, but those 2 combined should return a unique result anyway.
So is there a way to award you points or anything on this forum?
This is off topic, but it would be nice to convert this to nhibernate (like hql) instead of using a sproc.
As long as you don't have duplicate date values per my_app_id then you should be fine... If not we'd have to look for another solution.
No, you can't award points. This forum is about helping to get good performing SQL code, not points (Well, by writing this post I just get another point 😀 Ching!)
Regarding nhibernate: I don't know the options you have with that "language". But as long as this stuff is capable of doing something as simple as a subquery you could use the following code (it simply turn the CTE into a subquery). Other than that I truly hope you're a NH-Ninja, since I've seen some NH stuff that forced SQL code to take hours where plain SQL finsihed in seconds...
SELECT my_log_table.*
FROM my_log_table
INNER JOIN
(
SELECT my_app_id, MAX(last_update) AS max_last_upd
FROM my_log_table
GROUP BY my_app_id
) cte
ON my_log_table.my_app_id=cte.my_app_id
AND my_log_table.last_update=cte.max_last_upd
WHERE my_log_table.status='error'
May 26, 2010 at 12:54 pm
Indeed you are right about NH performance. I've fought with it from the beginning and still am. With just the right tweeks, and the proper alignment of stars, you can get it to perform decently. Thanks for the subquery, I'll use it instead since it's more cross-platform/database friendly.
May 26, 2010 at 4:21 pm
Glad I could help 🙂
(Even though I still have some headache when thinking about somebody being forced to use NH... 😉 )
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply