DISTINCT Clause - Can it be adapted to this requirement? 2

[Deadline]

Hi,

Consider this query :-

SELECT DISTINCT 
EmpID, TripNumber, EmpName, Destination
FROM EmployeeTravel

I want the DISTINCT Clause applied ONLY TripID. How to accomplish this ? Thank you...
RR

 

[tlbroadbent]

Do you mean TripID or TripNumber? There is a conflcit between your query and your question.

Could you provide a sample of the data and the result you want to see? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Deadline]

Hi,

Yeah.. I meant TripNumber. This dataset pertains to ASP + SQL Server

Sorry..

TripNumber   EmpID   EmpName  Destination
2            1       JOHN     NYC
1            1       JOHN     LON
1            1       JOHN     WAS

While displaying I will NOT be displaying the Destination, though I will be SELECTing it. ie,

SELECT TripNumber,EmpID,EmpName,Destination FROM TABLEA

This table can be sorted, by clicking on the Column Heading.

And, I am trying to display ONLY the records that have unique trip numbers. Thats why I asked whether there is a way to apply DISTINCT to one column alone..

Thank you...
RR

 

[tlbroadbent]

I still need clarification.

Is the data you showed the way it appears in the table or the way you want it to appear when selected? The phrase "ONLY the records that have unique trip numbers" causes me to believe that you only want TripNumber 2 to be selected. TripNumber 1 would be eliminated because it is not unique. Is that correct?

Or do you want TripNumber 1 to be returned but only once? If this is so, which destination should appear in the result set? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[ducksoup]

If I interpret correctly, you're organizing by trip number -- then allowing an interface to view the legs of that tripnumber, or whatever other data is in the table.

If so, 'select distinct tripnumber' into a cursor, then iterate the cursor to get the individual items of each tripnumber.

Hope that helps. __________________________________
You don't see with your eyes, you perceive with your mind.

 

[Deadline]

Thank you Terry, Thank you ducksoup.

Terry,

I have shown the data as present in the table.
When I make a query, I want ONLY ONE of the duplicates(either of the trip#1 ) to be eliminated. (The equivalent effect of using DISTINCT clause on tripnumber alone).


Many Thanks yet again... Thank you...
RR

 

[tlbroadbent]

Try this.

SELECT
TripNumber,
EmpID,
EmpName,
Max(Destination) As Destination
FROM TABLEA
Group By
TripNumber,
EmpID,
EmpName Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.