Table1 is having fromcity tocity is having distance of 400 km from hyderabad to bangalore, 400 km from bangalore to hyderabad, should get only single record

Question

1.00/5 (1 vote)

See more:

Promble:- Table1 is having FromCity ToCity is having Distance of 400 Km from Hyderabad to Bangalore,400 Km from Bangalore to Hyderabad,Should get only Single Record As Distance from Hyderabad to Banglore is 400 and Bangalore to Hyderabad is 400,
Eliminate the Row3 as No Duplicate record is not there for Warangal to Hyderabad.
Eliminate the Row4 as No Duplicate record is not there for Vijawada to Hyderabad.

Table1
ID FromCity ToCity Distance
1 Hyderabad Bangalore 400
2 Bangalore Hyderabad 400
3 Warangal Hyderabad 150
4 Vijawada Hyderabad 400

Expected Result :-Need to get only one Record Either Row1 or Row2 (No Duplicate Record) as Below

Row1
ID FromCity ToCity Distance
1 Hyderabad Bangalore 400

(OR)

Row2
ID FromCity ToCity Distance
2 Bangalore Hyderabad 400

What I have tried:

I have Concatenation FromCity + ToCity + Distance As ResultSet1, and ToCity + FromCity +Distance As Result Set 2

ResultSet1 ID
HyderabadBangalore400 1

ResultSet2 ID
HyderabadBangalore400 2

Now I am not geting Idea how to go further?Kindly give Some ideas on the Problem to Slove it

Posted 6-Apr-17 3:25am

Member 13005867

Updated 7-Apr-17 8:01am

v5

Add a Solution

2 solutions

Solution 1

Use the parameters you are searching for in your SELECT clause rather than the values returned from the database and you can then use DISTINCT to ensure you only get a single row.

Like this:

SQL

declare @city1 nvarchar(125) = 'Hyderabad'
declare @city2 nvarchar(125) = 'Bangalore'

SELECT DISTINCT @city1, @city2, Distance
FROM Table1
WHERE (FromCity = @city1 AND ToCity = @city2)
	OR
      (FromCity = @city2 AND ToCity = @city1)

[EDIT - Deal with Id column]
If you need a list of the Id's that matched the two cities then generate a CSV list of them e.g.

SQL

SELECT DISTINCT @city1 AS CITY1, @city2 AS CITY2, Distance,
	IDS = STUFF((SELECT ', ' + CAST(id as nvarchar) FROM Table1 
				WHERE (FromCity = @city1 AND ToCity = @city2)
				OR (FromCity = @city2 AND ToCity = @city1)
		FOR XML PATH('')),1,2,'')

	FROM Table1
	WHERE (FromCity = @city1 AND ToCity = @city2)
	OR
	(FromCity = @city2 AND ToCity = @city1)

Alternatively you can get the Id in separate columns by "cheating" with Min and Max (this assumes you will not be inserting duplicate entries onto the table):

SQL

SELECT DISTINCT @city1 AS CITY1, @city2 AS CITY2, Distance, MIN(Id), MAX(Id)
FROM Table1
WHERE (FromCity = @city1 AND ToCity = @city2)
OR
(FromCity = @city2 AND ToCity = @city1)
GROUP BY Distance

[EDIT] OP has clarified the expected results. The last query will get what you need, just choose either MIN(Id) or MAX(Id).

Posted 6-Apr-17 3:46am

CHill60

Updated 8-Apr-17 1:29am

v3

Comments

Member 13005867 6-Apr-17 10:11am

We will Get the Distinct values with Columns FromCity,ToCity,Distance But Expected Result is With ID Column of the Table,When ID is used in above Query we will get Two Rows in Result Set

CHill60 6-Apr-17 12:49pm

Which ID do you want?

Member 13005867 7-Apr-17 13:51pm

Question is Updated Now,Kindly Go through and Expected Result as Below

Expected Result :-Need to get only one Record Either Row1 or Row2 (No Duplicate Record) as Below

Row1
ID FromCity ToCity Distance
1 Hyderabad Bangalore 400

(OR)

Row2
ID FromCity ToCity Distance
2 Bangalore Hyderabad 400

CHill60 6-Apr-17 13:03pm

I'm about to go out so I've updated my solution with two other alternatives

Member 13005867 7-Apr-17 14:20pm

I have use for loop if Records are more,Check has to done on each Record wise.

CHill60 8-Apr-17 7:16am

Then why did you ask how to get a single record?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RAMASWAMY EKAMBARAM · Accepted Answer · 2017-04-06T18:52:00

Solution 2

SQL

select fromcity, tocity, min(distance) distance
from
(
select case when fromcity > tocity then tocity else fromcity end fromcity,
case when fromcity > tocity then fromcity else tocity end tocity, distance
from table1
) a
group by fromcity, tocity

/* 
the min(distance) would not be even needed if you are sure that there is no inconsistency in the table ie. distance between bangalore and hyderabad is same in both records
*/

Posted 6-Apr-17 18:52pm

RAMASWAMY EKAMBARAM

Comments

CHill60 7-Apr-17 4:13am

I can only give you the feedback that the OP gave me ... see their comments to my solution...
"We will Get the Distinct values with Columns FromCity,ToCity,Distance But Expected Result is With ID Column of the Table,When ID is used in above Query we will get Two Rows in Result Set"

Member 13005867 7-Apr-17 14:16pm

I have Just added ID column to you solution,Thank for the Solution.Modifyed Query is as Below.

select fromcity, tocity, min(distance) distance,Min(ID) ID
from
(
select case when fromcity > tocity then tocity else fromcity end fromcity,
case when fromcity > tocity then fromcity else tocity end tocity, distance,ID
from table1
) a
group by fromcity, tocity

Member 13005867 7-Apr-17 14:22pm

Thank U For Feasible Solution as it is not checking Record wise ,it is doing batch processing

Member 13005867 7-Apr-17 14:24pm

After I got the Solution from above query,I have tryed below query,But this did not gave me correct ans,Kindly Explain me your query how it is different from below query

select fromcity, tocity, min(distance) distance,Min(ID) ID
from table1
group by fromcity, tocity

CHill60 8-Apr-17 7:22am

You can't include distance in the SELECT unless you also include it in the GROUP BY.