Applying DISTINCT on given columns

Anon

Can I select distinct on only one column, but in the result recieve this column and the primary key of each records of given column.

Something like that:

SELECT ID_PRIMARY, (DISTINCT ID_NAME) ...

Anon

Distinct is always applied to all expressions in the select list.

Seems lika a improper database design if you have that need.

Anon

Well, I had the same question, and I don't think it's an improperly designed database, there are many times you would want to narrow down the scope using one set of fields, but have the returned array contain other fields as well.

Sam

Anon

Selecting a distinct value means you don't care how many rows the identical value appears in, you only want to see each distinct value for that column in the result set once (one row is returned for each distinct value). How could you return multiple values for the other columns and still keep the distinct column distinct? (Hint: you can't).

It sounds like what the poster really wants is to "order by" the column, not return the distinct set of values.

Anon

Well, before I jump on a plane to Boston

If I have 3 columns:
Category
Sex
Name

select distinct Category, Sex

will give me all categories extant for Male and Female (one way of looking at it). That has meaning to me. But if I say

select distinct Category, Sex, Name

then Even though Susie and Sally are both Farmers and not Carpenters, they are "distinct" -- so the data returned is not meaningful.

BUT -- BUT there are times where I want to know the name nevertheless, which is what the poster is asking, and the Name will NOT be a part of the collection returned. I'm on his side, I don't know if it can be done, but what he wants is certainly legit.

Sam Fullman
Compass Point Media

Anon

Why wouldn't ORDER BY work here? You are getting the same data, just minus the neat formatting.

Sounds like you are worried about how the data is being presented - such concerns are usually better handled outside the database.

Anon

I don't understand what you want.

"select distinct Category, Sex ..." will return a set of [Category,Sex] pairs, each member of that set (the result set) will have a unique (or distinct) pair of values.

"select distinct Category, Sex, Name ..." will return a set of [Category,Sex,Name] triples, each member of that set will also be unique.

The original poster wanted something like "select distinct(Category), Name, ..." but that makes no sense, you might have 3 distinct Categories but a total of five rows when combined with the Name column. How many rows would be in the result set? It can't be more than 3 because that's the number of distinct values, but it can't be less than 5 because that's how many [Category,Name] pairs exist.

mattr

tomhath and Lars are 100% correct.

Imagine a set like this:

Category, Sex, Name

"Carpenter", "F", "Sally";
"Carpenter", "M", "George";
"Truck Driver", "M", "Tim";
"Farmer", "M", "Bob";
"Farmer", "M", "Joe";

SELECT DISTINCT Category, Sex

"Carpenter", "F";
"Carpenter", "M";
"Truck Driver", "M";
"Farmer", "M";

So if you tried to make up a query like this:
SELECT DISTINCT( Category, Sex ), Name

You'd end up with something like this:
"Carpenter", "F", "Sally";
"Carpenter", "M", "George";
"Truck Driver", "M", "Tim";
"Farmer", "M", ??????????????

How does it know which name to pick? It can't. I will echo what the rest have said in that that type of requirement usually arises from improper database design or a lack of understanding of what you're trying to fetch.

Anon

Well, I have done the this thing by using the GROUP BY method:
SELECT ID_PRIMARYKEY, ID_ARTIST FROM SONGS GROUP BY ID_ARTIST

The table is

id_primarykey | id_artist
453463 | Royal Hunt
344634 | Royal Hunt
436663 | Papa Roach
...

The result is

id_primarykey | id_artist
453463 | Royal Hunt
436663 | Papa Roach
...

Anon

GROUP BY is not the right approach either, because you will be just returning one row per id_artist - you want multiple rows (all the id_primarykeys) per id_artist. GROUP BY artist, key ~= DISTINCT artist, key...

If you want to have the id_primarykey in an array, do that in a script!

Anon

SELECT DISTINCT ON (ID_NAME), * FROM ...

Chris

Anon

In databases that do support DISTINCT ON the syntax does not call for a comma after DISTINCT ON, as it isn\'t a part of the select list.

Even with that fixed, it will only return one row per id_name - you will be missing alot of data.