SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    na
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    strange mysql problem

    hello,

    i'm experiencing a strange mysql select problem, and was hoping one of you wizards might bring some insight into it. i'm running mysql 4.32.21

    i have a table with an author column which stores alpha names (bob, roger, etc). when i do a "select author from table order by date desc limit 10" i get: a, a, b, b, c, d, e, f, g, h. but when i do a "select distinct author from table order by date desc" i get: a, b, d, e, g, h, j.... it seems to skip c and f, and others as well.

    dropping the distinct shows the results fine (but shows unwated duplicates). using distinct drops the duplicates, but also consistenly doesn't return certain results.

    any ideas?

    thank you

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    show a few real rows, i gotta try this myself
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    na
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    not sure how to do this. i tried making up a temp table with the names and it's working as expected. but obviously im not using a temp table in my real use.

    any ideas what MIGHT be wrong?

  4. #4
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you get 10 results in your distict set?
    Does the same effect happen when you order by the author field?

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    na
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i get 10 results in my distinct set. the effect happens even i don't use order by / limit, or if i order by author.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    could you please explain in english what you want to get?

    "select author from table order by date desc limit 10" doesn't make sense to me

    what do you want the top 10 of? dates or authors?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    na
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    pardon, i want the last 10 (ordered by date) authors. i enter a timestamp into the db upon each new entry. so the query should sort the table by date desc and then grab the last 10 distinct authornames.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    > i want the last 10 (ordered by date) authors

    okay, the specific solution for that is --
    Code:
    select author 
      from ( select author
                  , date
               from yourtable
           order by date desc
              limit 10 )
    unfortunately, mysql doesn't do derived tables until release 4.1

    the inner query will give the desired results but include the date too (which, to me, isn't really a problem)

    you could use DISTINCT in the outer query, but then there might be fewer than 10

    > so the query should sort the table by date desc and then grab the last 10 distinct authornames

    that, unfortunately, is a totally different query

    what if the very last three were by the same author? then you'd want 13 returned by the inner query, so that when DISTINCT was applied, it would boil down to 10

    i have a feeling the solution is going to be something along these lines --
    Code:
    select author
      from yourtable X 
     where ( select count(distinct author) 
               from yourtable  
              where date < X.date ) < 10
    however, i have not tested this

    you may want to just sort by date descending, pull about 50 or so into your script (coldfusion, php, asp, whatever), and then do the distinct checking that way


    rudy

  9. #9
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    na
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for your help, rudy. i'll give this a go when i install 4.01 as i'm currently using 3.23.41.

    appreciate your time and help.


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
  •