Selecting Distinct
I have this statement:
Select j.JobId as ID, r.run as [Name], j.Route as Parent from jobs as j, run as r where r.runid = j.run
Which gives me:
63 D1 1
58 D1 1
64 D1 1
71 D1 1
57 D1 1
70 D1 1
65 D2 1
66 D2 2
67 D2 5
68 D2 2
69 D2 2
59 D2 1
60 D2 2
What I am looking to have
r.run as [Name] & j.Route as Parent
As being unique.
So for 1 we would only have D1 & D2 appear once. instead of having D1 appear multiple times.
Any ideas on this? I tried to do Distinct but I couldn't get it working correctly.
Select j.JobId as ID, r.run as [Name], j.Route as Parent from jobs as j, run as r where r.runid = j.run
Which gives me:
63 D1 1
58 D1 1
64 D1 1
71 D1 1
57 D1 1
70 D1 1
65 D2 1
66 D2 2
67 D2 5
68 D2 2
69 D2 2
59 D2 1
60 D2 2
What I am looking to have
r.run as [Name] & j.Route as Parent
As being unique.
So for 1 we would only have D1 & D2 appear once. instead of having D1 appear multiple times.
Any ideas on this? I tried to do Distinct but I couldn't get it working correctly.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try this query
Select j.JobId as ID, A.[Name], A.Parent from jobs as j, run as r where r.runid = j.run
Right Outer Join (
Select Distinct r.run as [Name], j.Route as Parent from jobs as j, run as r where r.runid = j.run) A
On A.[Name] = r.run Or A.Parent = j.Route
Try also using if it gives your desired result
Select j.JobId as ID, A.[Name], A.Parent from jobs as j, run as r where r.runid = j.run
Left Outer Join (
Select Distinct r.run as [Name], j.Route as Parent from jobs as j, run as r where r.runid = j.run) A
On A.[Name] = r.run Or A.Parent = j.Route
ASKER
I really would need an ID field in there as my code relies on it. How would I have the Distinct and pull out the most recent ID (eg: the largest number)
71 D1 1
67 D2 5
68 D2 2
71 D1 1
67 D2 5
68 D2 2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cheers needed to make some minor changes:
Select Max(j.JobId) as ID, r.run as [Name], j.Route as Parent from jobs as j, run as r where r.runid = j.run group by r.run, j.Route
Select Max(j.JobId) as ID, r.run as [Name], j.Route as Parent from jobs as j, run as r where r.runid = j.run group by r.run, j.Route
Select r.run as [Name], j.Route as Parent , count(j.JobId) from jobs as j, run as r where r.runid = j.run
group by r.run,j.Route