asked on

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.

SOLUTION

Binuth

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

Pratima

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Pratima

if you want how many jobs in this unoqe combination then

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

Muhammad Kashif

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

Open in new window

Muhammad Kashif

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

Open in new window

directxBOB

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

ASKER CERTIFIED SOLUTION

Muhammad Kashif

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

directxBOB

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