Distinct command doesn't work

This is the command:

SELECT DISTINCT T.TRIP_ID, T.TRIP_NAME, T.STATE, T.TYPE, S.TRIP_ID, S.TRIP_NAME

FROM TRIP T, TRIP S

WHERE T.STATE = 'NH'

AND T.TYPE = 'Hiking'

AND T.TRIP_ID < S.TRIP_ID

ORDER BY T.TRIP_NAME;

I should only getting about 6 results but somehow I get 298 repeated results. What's the problem with this code?

Correct result (6 entries)

My result (first few entries) which totals to 298 entries

Upvotes: 1

Answers (3)

SandPiper

Reputation: 2906

I'm not sure why you are doing a self join in this example. But if you want just the distinct trip names in New Hampshire that involved hiking, do this:

SELECT DISTINCT T.TRIP_NAME
FROM TRIP T
WHERE T.STATE = 'NH' AND T.TYPE = 'Hiking' 
ORDER BY T.TRIP_NAME;

Upvotes: 1

KJBoffo

Reputation: 41

THE DISTINCT keyword operates on ALL select-list items that follow it, not just the next immediate column. So, in your case, it IS (or would be) removing duplicates when ALL columns are considered. The results you show all look individual over all the columns.

Upvotes: 1

Roger Cornejo

Reputation: 1547

you have a missing join condition by usual convention.

Upvotes: 0

Distinct command doesn&#39;t work

Answers (3)

Related Questions

Distinct command doesn't work