SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Distinct select ordered by unselected column

    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
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  2. #2
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,283
    Mentioned
    121 Post(s)
    Tagged
    1 Thread(s)
    Just remove the ON and the query should work like you want it to. Try:

    Code:
    SELECT DISTINCT Genre 
      FROM Genres
     ORDER BY ID
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  4. #4
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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).

  5. #5
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,283
    Mentioned
    121 Post(s)
    Tagged
    1 Thread(s)
    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
    Code:
    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:
    Code:
    SELECT Genre, Count(*) as GenreCount
      FROM Genres
     GROUP BY Genre
     Order BY GenreCount Desc
    NOTE: This may not work on PostGreSQL, but it should....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  7. #7
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •