Distinct select ordered by unselected column

[Flawless_koder]

Ok simplified scenario:

1 table:

2 Fields:

id ( serial )
genre ( varchar(20) )


I want to SELECT genre only from the table, but need it to be DISTINCT (on genre, obviously). I also need to order it by id ASC. I've found i can't do this even if i include the id in the SELECT since it wants the id to be distinct.

Does anyone have any ideas?

Thanks

Flawless

[Flawless_koder]

From discussions with Mullie it seems this is completely impossible without returning the id, and even then it's difficult.
Aparently creating a view doesn't help... sooo...
if anyone can pull this off returning the id as well please DO help...
I've tried this and it sparks an error:

SELECT DISTINCT ON (genre) genre, id FROM genres ORDER BY id;

( error:
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions)

Any ideas anyone?

Flawless

[DaveMaxwell]

Just remove the ON and the query should work like you want it to. Try:

SELECT DISTINCT Genre 
  FROM Genres
 ORDER BY ID

[mulletboy2]

Dave - Which DBMS will that work in? Postgres/SQL Server will return an error that the "Order By" fields must be included in the Select clause, and including the ID that way will mean all records will be seen as distinct, as the ID is different for each one.

I understand from flawless that the problem is sorted out now.. he managed to get the ON clause working, so could perform a distinct on one field (genre), even though he was selecting 2 fields (genre and ID).

[Flawless_koder]

Dave - you can't (from what i've seen in PgSql) order by a field alien to the the distinct, since distinct orders itself.

Right - well there's two things i've got to add to this now.

1) this can be solved using a view:

create view s_genres AS select distinct on (genre) genre, id from genres;

and then a select:

select genre from s_genres order by id;

2) I've decided to add a third field to the table to make this possible using a direct query.

Flawless

[DaveMaxwell]

Oh! I'm an idiot!

What you just posted didn't make a whole lot of sense at first. All DISTINCT does is ensure that only one row of a particular value is returned. There is really no sorting involved.

The problem you are getting is that you can't order by a field not having the distinct value attached to it since the server doesn't know which of the non-distinct values to order on. I'll show an example to try and explain it further:

Say you have these values in your table

Row Number  |  ID  |  Genre
1               1     Rock
2               2     Rock
3               3     Classical
4               4     Classical
5               5     Rock
6               6     Jazz
7               7     Jazz
8               8     Techno
9               9     Techno

Now your distinct should return the following: Rock, Classical, Jazz, Techno

You want to sort it by ID, but since there is no distinct on that field, should it pick Row 1,2 or 5 for the ID to sort it off of? If it picks 1 or 2, the order will stay the same as I just listed. If if picks 5, the order will now be: Classical, Rock, Jazz, Techno

Which one is correct? The server has no way to know.
Are you looking to sort it by whichever has MORE records on the table? If so, I think an easier approach would be for you to select the table like this:

SELECT Genre, Count(*) as GenreCount
  FROM Genres
 GROUP BY Genre
 Order BY GenreCount Desc

NOTE: This may not work on PostGreSQL, but it should....

[mulletboy2]

flawless - if your view method worked, won't this work too?

SELECT genre
FROM genres
WHERE id IN (
SELECT DISTINCT ON (genre) id FROM genres
)
ORDER BY id

The syntax for "DISTINCT ON" is alien to me, but it looks like it should work according to your previous posts.

[Flawless_koder]

Dave - you're approach doesn't work.
I'm not sure why - but when i tried it out - it didn't work.

Mullie - works FINE, thanks


However as I said to mullie, and in the last post (2),
I've added a third field to allow for them to be pulled easily.

Flawless