January 21, 2010 at 3:36 pm
Hi everyone, I need some help building a query on a table.
This is the table:
Guid | Level| lastchanged
B35A29DF-57C6-4428-B33C-000F3EE7ED5910016:26.3
B35A29DF-57C6-4428-B33C-000F3EE7ED5910021:18.7
B35A29DF-57C6-4428-B33C-000F3EE7ED5910026:11.4
B35A29DF-57C6-4428-B33C-000F3EE7ED5910031:02.3
B35A29DF-57C6-4428-B33C-000F3EE7ED5910035:54.3
B35A29DF-57C6-4428-B33C-000F3EE7ED5910040:46.3
B35A29DF-57C6-4428-B33C-000F3EE7ED5910045:39.6
DD61A7F9-C6B7-4719-B2D2-00DF6A2BBD5A10016:34.5
DD61A7F9-C6B7-4719-B2D2-00DF6A2BBD5A10022:29.3
DD61A7F9-C6B7-4719-B2D2-00DF6A2BBD5A10028:21.7
EA8E7063-1FA6-4200-826D-0106E52E9B4A10031:28.5
EA8E7063-1FA6-4200-826D-0106E52E9B4A10020:36.4
EA8E7063-1FA6-4200-826D-0106E52E9B4A10024:57.2
EA8E7063-1FA6-4200-826D-0106E52E9B4A10038:38.5
EA8E7063-1FA6-4200-826D-0106E52E9B4A10029:20.9
CA73CCF4-2943-4FF6-ABA4-024607CB97B18035:40.0
CA73CCF4-2943-4FF6-ABA4-024607CB97B110049:22.5
What I need to do is, based on lastchanged field, compare the level field from the last too entry's for each guid. This is, for same guid. If the 2 Levels are the same, disregard that guid, if they are different, show the guid. The only output necessary is the guid.
So from the posted data the only record it should output should be:
GUID
CA73CCF4-2943-4FF6-ABA4-024607CB97B1
Because it's 2 last level values are different.
I've been trying to figure this out without using cursors because I'm no that familiar with them but haven't figured it out, one idea was to use a sub-select with a top 2, but I didn't get it to work.
Any Ideas?
Thanks a lot in advanced
Lucas
January 21, 2010 at 4:17 pm
I would use the following code.
The first CTE is used to order each guid per lastchanged column (desc order).
The second CTE calculates the sum of the latest two rows.
The final SELECT statement displays the latest two rows, where the Level value is different than half of the sum calculated before.
Did you notice how I set up the sample data so it can easily be used by others who'd like to have a look at it as well?
DECLARE @t TABLE (Guid_ varchar(36), Lvl int, lastchanged char(8))
INSERT INTO @t
SELECT 'B35A29DF-57C6-4428-B33C-000F3EE7ED59', 100 ,'16:26.3' UNION ALL
SELECT 'B35A29DF-57C6-4428-B33C-000F3EE7ED59', 100 ,'21:18.7' UNION ALL
SELECT 'B35A29DF-57C6-4428-B33C-000F3EE7ED59', 100 ,'26:11.4' UNION ALL
SELECT 'B35A29DF-57C6-4428-B33C-000F3EE7ED59', 100 ,'31:02.3' UNION ALL
SELECT 'B35A29DF-57C6-4428-B33C-000F3EE7ED59', 100 ,'35:54.3' UNION ALL
SELECT 'B35A29DF-57C6-4428-B33C-000F3EE7ED59', 100 ,'40:46.3' UNION ALL
SELECT 'B35A29DF-57C6-4428-B33C-000F3EE7ED59', 100 ,'45:39.6' UNION ALL
SELECT 'DD61A7F9-C6B7-4719-B2D2-00DF6A2BBD5A', 100 ,'16:34.5' UNION ALL
SELECT 'DD61A7F9-C6B7-4719-B2D2-00DF6A2BBD5A', 100 ,'22:29.3' UNION ALL
SELECT 'DD61A7F9-C6B7-4719-B2D2-00DF6A2BBD5A', 100 ,'28:21.7' UNION ALL
SELECT 'EA8E7063-1FA6-4200-826D-0106E52E9B4A', 100 ,'31:28.5' UNION ALL
SELECT 'EA8E7063-1FA6-4200-826D-0106E52E9B4A', 100 ,'20:36.4' UNION ALL
SELECT 'EA8E7063-1FA6-4200-826D-0106E52E9B4A', 100 ,'24:57.2' UNION ALL
SELECT 'EA8E7063-1FA6-4200-826D-0106E52E9B4A', 100 ,'38:38.5' UNION ALL
SELECT 'EA8E7063-1FA6-4200-826D-0106E52E9B4A', 100 ,'29:20.9' UNION ALL
SELECT 'CA73CCF4-2943-4FF6-ABA4-024607CB97B1', 80 ,'35:40.0' UNION ALL
SELECT 'CA73CCF4-2943-4FF6-ABA4-024607CB97B1', 100 ,'49:22.5'
;with cte as
(
SELECT
row_number() over(partition BY Guid_ ORDER BY lastchanged desc) row,
*
FROM @t
),
cte2 as
(
SELECT
guid_,
sum(lvl) AS total
FROM cte
WHERE row<3
GROUP BY guid_
)
SELECT cte.* FROM cte2
INNER JOIN cte
ON cte.guid_=cte2.guid_
AND row<3
AND lvl<>total/2
/* result set
rowGuid_Lvllastchanged
1CA73CCF4-2943-4FF6-ABA4-024607CB97B110049:22.5
2CA73CCF4-2943-4FF6-ABA4-024607CB97B18035:40.0
*/
January 22, 2010 at 12:13 pm
Lutz,
First of all, thanks for your reply, and sorry for not putting the creation code for the data, I didn't realize about that.
Now, I'm not sure why you added the second cte to add both latest rows.
What I need to obtain is the "distinct" list of GUID's were the last two recorded level's, based on the date, are different. I result the fact that they are distinct because on the result list each GUID can appear once.
Thanks
Lucas
January 22, 2010 at 12:31 pm
lcibert (1/22/2010)
Lutz,First of all, thanks for your reply, and sorry for not putting the creation code for the data, I didn't realize about that.
Now that you know how you'll make it easy for us to help you I'm sure you'll do it next time. Since you're kinda new to this forum there's no reason to be sorry for. You just didn't know. 😉
Now, I'm not sure why you added the second cte to add both latest rows.
Basic math:
sum(lvl) = last level recorded + previous level recorded.
If sum(lvl) / 2 = value for one row in the first cte then the second value of that guid_ has to be the same - and therefore that guid_ will be excluded.
The current result is a list of all guids with its corresponding levels.
If you'd need just the guid, change the final SELECT to
SELECT cte.guid_ FROM cte2
INNER JOIN cte
ON cte.guid_=cte2.guid_
AND ROW<3
AND lvl<>total/2
GROUP BY cte.guid_
--result: CA73CCF4-2943-4FF6-ABA4-024607CB97B1
I decided to include the level value in my original reply to show that the query got proper results. If you have sample data that case my query to fail please provide the INSERT statement and I'll look into it.
Edit: different way of explaining the math:
last level = a
previous level = b
sum()= a + b = c
If a = b then a + b = a + a = 2 * a = c
Therefore, If c/2 = a then b = a. (both levels are identical, guid needs to be excluded).
January 22, 2010 at 1:30 pm
I had got mixed up with other fields because this is a very large Table, but your Query works great!
Thanks a lot!
Lucas
January 22, 2010 at 2:09 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply