DISTINCT keyword question. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

DISTINCT keyword question.

Author

Topic

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-07-11 : 15:45:14

Ok, so I have this query:

SELECT DISTINCT atf.LevelCode LC, ot_l.ShortDescription De, atf.FormID FID FROM AssessmentToFFL atf JOIN
OT_Level ot_l ON (atf.LevelCode = ot_l.OptionCode)
WHERE atf.InstrumentID = 106
ORDER BY atf.FormID

My OT_Level table has the following columns:
ShortDescription, OptionCode

My AssessmentToFFL table has the following columns:
LevelCode, OptionID, InstrumentID and a few others that I don't use.

Now, with the distinct keyword in there I get output like this:
LC___De___FID
102 Some 125
103 Some2 125
104 Some3 125
102 Type 126
103 Type1 126
104 Type3 126

and so on.
Now my question is, what the heck is going on here?
--Nick

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-11 : 15:48:53

What should the query output look like?

Tara Kizer
aka tduggan

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-07-11 : 15:54:53

I'm not sure what I was expecting the output to be on this one (I was just messing around with things and really answering any questions). It does give me useful information, but I was just wondering what SQL Server is doing here.
--Nick

It does give me all the unique levels/description of all the forms. Which can answer the question: What are all the levels/descriptions for all the forms.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-12 : 02:26:44

DISTINCT will remove duplicate rows if they are identical. It seems the sample data dont have duplicate rows and you didnt tell us what you are actually expecting to get

Madhivanan

Failing to plan is Planning to fail

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-07-12 : 12:08:10

Nick,

You were expecting only one row for each levelcode because of the DISTINCT, weren't you? :-)

DISTINCT applies to the combination of all specified columns, not only the one immediately after DISTINCT.

Ken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources