SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    can you use union to de-dupe but preserve order by?

    Hi,

    I'm using a UNION DISTINCT to remove duplicates between two sets. However, its important for me to preserve the sort order from each of the individual selects, AND have the results of the two SELECTs appear in order (ie all the results of the first select, followed by the second select).

    The query looks like this:

    (SELECT watch_list.user_id AS watch_flag, id, board_id, num_replies, create_date, modify_date
    FROM threads, watch_list
    WHERE board_id IN $selected_boards
    AND threads.id = watch_list.thread_id
    AND watch_list.user_id = '$uid'
    ORDER BY create_date DESC)

    UNION

    (SELECT '$uid' AS watch_flag, id, board_id, num_replies, create_date, modify_date
    FROM threads
    WHERE board_id IN $selected_boards
    ORDER BY create_date DESC)

    Is there any way to do this?

    Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    could you explain why there would be duplicates between the two SELECTs

    this concept does not work if you want to "preserve sort order"

    if there actually is a row produced by the first SELECT that is also in the second SELECT, how do you determine which of them you want the dupe removal to remove?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    could you explain why there would be duplicates between the two SELECTs

    this concept does not work if you want to "preserve sort order"

    if there actually is a row produced by the first SELECT that is also in the second SELECT, how do you determine which of them you want the dupe removal to remove?
    The query is used to pull a list of threads from a forum. The first SELECT pulls the user's 'subscribed' threads, and the second query pulls all the threads. I union the two and want to show subscribed threads followed by all other threads, but without de-duping, the same thread will show up again via the second SELECT. Hope that makes sense.

    In this case I'd want to keep the first select's row but I understand your point. Maybe I can find a way to modify the second select to not include those thread ids...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    why not just pull all the threads with one SELECT, mark the ones that the user is subscribed to, and then sort them that way

    that seems a lot simpler to me, and i *heart* simple
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    why not just pull all the threads with one SELECT, mark the ones that the user is subscribed to, and then sort them that way

    that seems a lot simpler to me, and i *heart* simple
    The subscribe list is in a separate table, do you mean use a left outer join between the threads and subscribes tables to "mark" the subscribed ones?

    That is what I initially tried to do, but performance was really poor due to excessive filesorts.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,330
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jacksonheights View Post
    That is what I initially tried to do, but performance was really poor due to excessive filesorts.
    excessive filesorts???

    seems to me like it would be doing only one sort

    whereas your union would do three (one for each SELECT, and one to detect dupes)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    May 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    excessive filesorts???

    seems to me like it would be doing only one sort

    whereas your union would do three (one for each SELECT, and one to detect dupes)

    you're right. I took another look at the original query and I was doing some stupid subselects. thats what happens when I write these things at 2 in the morning thanks for your 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
  •