Odd return results - problem with query?
-
I am attempting to sort data in a table to where I would get the latest date with the unique CWSRouteID along with it. I have a sample of the table below.
CWSRouteID CWSID RouteState ActionDate 1 40 3 1/1/2007 2 40 1 2/1/2007 11 40 3 3/1/2007 12 40 0 NULL 13 40 0 NULL 14 40 0 NULL 3 42 3 1/1/2007 4 42 0 NULL 5 44 0 NULL 6 44 0 NULL 9 45 0 NULL 10 45 0 NULL 21 45 0 NULL 7 46 0 NULL 8 46 0 NULL 15 47 3 1/1/2007 16 47 0 NULL 17 47 0 NULL 19 48 1 1/1/2007 20 48 0 NULL Now when I attempt to run my query, I don't receive all the proper rows
What I want to return is
CWSRouteID CWSID ActionDate 11 40 3/1/2007 3 42 1/1/2007 15 47 1/1/2007 19 48 1/1/2007 select (a.cwsrouteid), a.cwsid, max(a.actiondate) from tblcws_routing A join tblcws_routing B on (a.cwsid = b.cwsid)
where a.actiondate is not null and a.cwsrouteid > b.cwsrouteid -- and a.routestate = 1 or a.routestate = 3
group by a.cwsrouteid, a.cwsid
Instead I'm returning this
CWSRouteID CWSID ActionDate 2 40 3/1/2007 11 40 1/1/2007 What am I doing wrong here? Any guidance is much appreciated!
-
It's the "and a.cwsrouteid > b.cwsrouteid" clause that is eliminating CWSIDs 42, 47, 48.
CWSID 40 gets returned because of CWSRouteID 1. Take out CWSRouteID 1 and you won't get any rows at all.
Regards,
Rubes -
Good suggestion Rubes. I am one step closer to the results I want, but I'm not sure how to eliminate the superfilous CWSID 40 records as I'm just wanting the latest date. Maybe a nested select statement is needed?
select max(a.cwsrouteid), max(a.cwsid), max(a.actiondate) from tblcws_routing_baggot A join tblcws_routing_baggot B on (a.cwsid = b.cwsid)
where a.actiondate is not null
group by a.cwsrouteid, (a.cwsid)
cwsrouteid cwsid Actiondate 1 40 1/1/2007 2 40 2/1/2007 3 42 3/1/2007 11 40 4/1/2007 15 47 5/1/2007 19 48 6/1/2007 -
HA! I believe I got it.
select max(a.cwsrouteid), max(a.cwsid), max(a.actiondate) from tblcws_routing_baggot A join tblcws_routing_baggot B on (a.cwsid = b.cwsid)
where a.actiondate is not null
group by (a.cwsid)
-
Terrence,
Your query may produce unwanted results. First of all, you don't need the self-join. How do I know this? Well, you are not using table B for any reason and if you eliminate the JOIN entirely, your query produced the same results. Aside from that, they way you are aggregating all of the columns will give you invalid results as you are not bringing back one row. You are instead bringing back the MAX values for all columns in the group. For example, here is your query with your sample data slightly altered. Below that is my example which will always give you the correct results based on what you've requested so far:
--Create test table
DECLARE @tblcws_routing_baggot TABLE (CWSRouteID int, CWSID int, RouteState int, ActionDate datetime)
--add samle data to test table
INSERT INTO @tblcws_routing_baggot
SELECT 1, 40, 3, '1/1/2007' UNION ALL
SELECT 2, 40, 1, '2/1/2007' UNION ALL
SELECT 11, 40, 3, '3/1/2007' UNION ALL
SELECT 12, 40, 0, '2/1/2007' UNION ALL -- changed this from a NULL to a non-MAX value
SELECT 13, 40, 0, NULL UNION ALL
SELECT 14, 40, 0, NULL UNION ALL
SELECT 3, 42, 3, '1/1/2007' UNION ALL
SELECT 4, 42, 0, NULL UNION ALL
SELECT 5, 44, 0, NULL UNION ALL
SELECT 6, 44, 0, NULL UNION ALL
SELECT 9, 45, 0, NULL UNION ALL
SELECT 10, 45, 0, NULL UNION ALL
SELECT 21, 45, 0, NULL UNION ALL
SELECT 7, 46, 0, NULL UNION ALL
SELECT 8, 46, 0, NULL UNION ALL
SELECT 15, 47, 3, '1/1/2007' UNION ALL
SELECT 16, 47, 0, NULL UNION ALL
SELECT 17, 47, 0, NULL UNION ALL
SELECT 19, 48, 1, '1/1/2007' UNION ALL
SELECT 20, 48, 0, NULL
-- Your original Query (minus the self-JOIN)
select max(a.cwsrouteid), max(a.cwsid), max(a.actiondate)
from @tblcws_routing_baggot A
-- join @tblcws_routing_baggot B
-- on (a.cwsid = b.cwsid)
where a.actiondate is not null
group by (a.cwsid)
-- give my query a try
SELECT t1.CWSRouteID, t1.CWSID, t1.ActionDate
FROM @tblcws_routing_baggot t1
INNER JOIN (
SELECT CWSID, MAX(ActionDate) as MaxActionDate
FROM @tblcws_routing_baggot
WHERE ActionDate IS NOT NULL
GROUP BY CWSID
) t2
ON t1.CWSID = t2.CWSID AND t1.ActionDate = t2.MaxActionDate
ORDER BY t1.CWSID
-
John,
I definitely see your point here. My join didn't ensure any checks or reliability. Thank you very much for the query you provided as I was having issue with wrapping my head around this one.
Terrence
-
Ya beat me to it, John.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply