distinct problem - 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

SQL Server Development (2000)

distinct problem

Author

Topic

Kanati
Starting Member

36 Posts

Posted - 2002-12-13 : 17:08:54

I have a table... lets define it as "TABLE1"

primaryssn name
123456789 dan
123456789 dan2
234567890 eric
234567890 eric2

I want to return the entire record where the primaryssn is unique.
but obviously select distinct primaryssn, name returns all 4 records.
so I tried select * from (select distinct primaryssn from table1) t1
which didn't help much either since the derived table is only returning the primaryssn...

how can I return (using a single query if possible):

123456789 dan
234567890 eric

(just the first of the distinct primaryssn records for each name)

this is driving me nuts...

Thanks.
Kanati

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-12-13 : 17:21:21

quote:
just the first of the distinct primaryssn records for each name

There is no concept of first in a relational database. You can get the record with the highest or the lowest value in the name column


select primaryssn,min(name)
  from table1
 group by primaryssn

Edited by - LarsG on 12/13/2002 17:21:40

Kanati
Starting Member

36 Posts

Posted - 2002-12-13 : 17:33:31

thanks... but I need primaryssn to drive the query.

There are actually a lot more columns than name in the real-world case. I just didn't make myself clear really... maybe this is better.

table1

c1 c2 c3 c4 c5 c6
1 b 1 a 4 2
2 b 1 b 5 b
2 d 1 c 6 s
3 d 1 d 6 4
4 d 1 e 6 5
4 f 1 f 5 1

C1 drives the query... I need the distinct c1 records... all columns
the result set I want would then be.

1 b 1 a 4 2
2 b 1 b 5 b
3 d 1 d 6 4
4 d 1 e 6 5

the dupes (by c1's account anyway) would be ignored in the resulting recordset.

thanks again.
Kanati

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-12-13 : 17:53:34

I think something like this will work:


SELECT mt.C1, (SELECT TOP 1 C2, C3, C4, C5, C6 FROM Table1 WHERE C1 = mt.C1)
FROM Table1 mt
GROUP BY mt.C1

BTW... If you have fields named C1, C2, C3 etc that usually indicates that you need to normalize your table or you should probably come up with more descriptive names for your fields. I'm assuming these fields probably propagated from some legacy system.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-13 : 18:08:26

(I think c1..c10 were examples of columns 1-10)

First off, you need to define some rules. I assume you have data like this:


SS#         Name     Address      City     State    
123-12-1234 Joe      1 Elm St     Boston   MA
123-12-1234 Joey     1 Elm Street BOS      Massachusetts
222-22-2222 Ed       10 Main Rd   NY       NY
222-22-2222 Edward   10 Main Road New York NY

and you are saying "I want only the first record for each SS# w/o dups."

If I am with you so far, then here's the problem:

How do you define what is the "first?" Do you want "Joe" or "Joey"? do you want "Elm Street" or "Elm St"? What if every field is different except for your SS# field?

Now, if you say, "for each SS# I want the MIN of all the fields" then that is easily done. BUt it may return some data from all of the different records mixed together. is that ok?

Or, do you want to take the MIN of the name field, and use THAT record as your "true" record to return? If so, what happens if two SS#'s have the same name but other fields differ?

I hope you see what I am getting at.

You need to define some rules of exactly what you are looking for.

Is there an "ID" identity field or "date/timestamp" field in your table? In that case, you can say "for each SS#, give me the MIN() of ID or datestamp, and return THAT record" or something like that.

But you need some logical rules on what you would like returned. And you need a consistent way to tell the records apart in the table since the SS#'s match and other fields may or may not match.

I hope I am making sense!

- Jeff

Kanati
Starting Member

36 Posts

Posted - 2002-12-13 : 18:09:29

that does what I needed. thanks!

(those column names are just simplified... not the real ones we are using. Just wanted to make the problem as simple as possible.)

thanks again.

Kanati
Starting Member

36 Posts

Posted - 2002-12-13 : 18:12:32

thanks jeff. I see what you mean and I will do some tests with the query above to make sure it does what I want, but I think I can add some more contraints to it from there.

Kanati

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-13 : 19:08:18

quote:
(those column names are just simplified... not the real ones we are using. Just wanted to make the problem as simple as possible.)

Not to be critical, but as you can see, when you simplify, generalize, or abbreviate the question you're posting, it requires a lot of extra work for you and us when the original reply doesn't work because "something" was left out or not fully described. It would be better if you provided as much information as possible in your original post, you'll get a correct answer that much faster.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources