Selecting Distinct Value
I have this SQL Statement:
select distinct r.route as [Name],j.route as Route,j.run as Run,'NULL' AS Parent,(cast(j.route as varchar(64)) + cast(j.run as varchar(64))) as [UID],'Routes' as [Type]
from jobs as j,[route] as r
where j.date = '12/08/2008' and j.van = 1 and r.id = j.route
Essentially what I am trying to do is extract the Distinct Route, but present the data in the form:
Name, Route, Run, Parent, UID, Type
But somewhere along the line my SQL has gotten a bit mistaken and I've been staring at my statements too long to even spot a solution.
select distinct r.route as [Name],j.route as Route,j.run as Run,'NULL' AS Parent,(cast(j.route as varchar(64)) + cast(j.run as varchar(64))) as [UID],'Routes' as [Type]
from jobs as j,[route] as r
where j.date = '12/08/2008' and j.van = 1 and r.id = j.route
Essentially what I am trying to do is extract the Distinct Route, but present the data in the form:
Name, Route, Run, Parent, UID, Type
But somewhere along the line my SQL has gotten a bit mistaken and I've been staring at my statements too long to even spot a solution.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
when you have several rows with the same route, but non-distinct values for the other columns, which rule do you want to apply to (let sql) determine which of the rows to returns?
ASKER
yeah I can let SQL determine it or I can preset most of them.
Route as [Name]
r.route.id as Route
These are both the same row they are just the name and id.
Run can be set to 0 as it's not important I just maintain it to ensure consistency when building up my other queries. Parent should be NULL and UID can be the Route.ID
Route as [Name]
r.route.id as Route
These are both the same row they are just the name and id.
Run can be set to 0 as it's not important I just maintain it to ensure consistency when building up my other queries. Parent should be NULL and UID can be the Route.ID
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.