Distinct Problems - 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 Problems

Author

Topic

Faiyth
Starting Member

19 Posts

Posted - 2003-06-02 : 19:30:17

I'm trying to do a distinct select, I've tried doing it several ways, all of which aren't working.. Here's an idea what the data looks like..

Mock Data:

Animal - Type
Rat - Rodent
Mouse - Rodent
Pig - Farm
Horse - Farm
Deer - Wildlife
Elk - Wildlife
Snipe - Unknown
Pegasus - Mythical

I want it to combine the type of animal with the very first one, so I'd get distinct results from the animal type but also return the "Animal" as well. I tried grouping but I'm having problems grouping Animal because they are all Distinct.

Here's a sample of what I want to show: (notice that it took the first occurance of each "type")
Animal - Type
Rat - Rodent
Pig - Farm
Deer - Wildlife
Snipe - Unknown
Pegasus - Mythical

Am I just absolutely asking for the moon and stars here?

~ Amber

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-02 : 19:38:33

This won't give you the first occurrence, but it will give you one record for each type:

SELECT MAX(Animal) AS Animal, AnimalType
FROM Animal
GROUP BY AnimalType

Tara

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-06-02 : 20:11:53

What Tara says is right. You don't want distinct, what you really want is just the first Animal for each type, (implicitly using the order in which they appear in the list).

Unfortunately, since in a relational database, order is not inherent in the storage of the records, you really need to assign which order to use to pick the first of each group - (or you could use alpabetic ordering )

using alphabetic ordering .....

if you do

select max(animal) as Animal, type
from animals
group by type

you'll get
Pig Farm
Pegasus Mythical
Rat Rodent
Snipe Unknown
Elk Wildlife

which is not precisely what you want (it's in order of the type field).

if you do
select min(animal) as Animal, type
from animals
group by type

you'll get
Deer Wildlife
Horse Farm
Mouse Rodent
Pegasus Mythical
Snipe Unknown

Which is also, not precisely what you asked for.

If you do

select max(animal) as Animal, type
from animals
group by type
order by max(animal)

you'll get
Elk Wildlife
Pegasus Mythical
Pig Farm
Rat Rodent
Snipe Unknown

and at least now you're being explicit about what order you want returned (in order of the animal name).

None of these is exactly what you wanted. If the order is important you'll need to work out some way of deciding which order to state your results (possibly by adding some kind of orderering field to your table).

Also, you're not guaranteed to get the animal you want out of the possible options (eg Pig or Horse for type "farm") - what if there were three "farm" animals???

Instead, if you had:

Animal, Type, Suborder
Rat, Rodent, 1
Mouse, Rodent, 2
Pig, Farm, 1
Horse, Farm, 2

Then you could do
select a.animal, a.type
from animals a inner join (
select type, min(subOrder) as suborder
from animals
group by type
) b
on a.type = b.type and a.suborder = b.suborder

Notice that I've joined the animals table to a query which just picks out the first of each type.

At this point you'll probably notice that the code above is exactly the same as saying

select animal, type
from animals
where suborder = 1

which, although it is a lot simpler, still doesn't give you exactly what you asked for!!!.

However, the inner join on the subgroup can give you exactly what you want, if you just add a simple numbering to your table as below:

Animals
ListOrder, Animal, Type
--------------
1, 'Rat', 'Rodent'
2, 'Mouse', 'Rodent'
3, 'Pig', 'Farm'
4, 'Horse', 'Farm'
5, 'Deer', 'Wildlife'
6, 'Elk', 'Wildlife'
7, 'Snipe', 'Unknown'
8, 'Pegasus', 'Mythical'

select a.animal, a.type
from animals a inner join (
select type, min(ListOrder) as ListOrder
from animals
group by type
) b
on a.type = b.type and a.ListOrder = b.ListOrder
order by a.ListOrder

which yields:

Rat Rodent
Pig Farm
Deer Wildlife
Snipe Unknown
Pegasus Mythical

...as required.

Note the final "order by" clause which takes the result groups and reorders by their original order in the table.

This is much simpler solution since you don't have to restart the numbering for each group.

However, you probably just wanted Tara's solution, so I'll go back to my cup of tea and leave you alone.

Hope that helps

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 06/02/2003 20:20:14

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-03 : 18:35:09

Wow ... great post !

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources