SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Complex sort question

    Currently I have a members table with a lastvisit int(10) field.

    When member's search for others members the results are ordered by lastvisit, eg:
    select * FROM members ORDER BY lastvisit DESC

    I'm introducing a new field, hiddentime, so member's can hide and be invisible to other members.

    This works fine through out the site, except when it comes to ordering in search.
    hiddentime is also an int(10) and set to 0 when not hidden. This stores the time they "go into hiding" and returns to "0" when they stop playing hide and seek.

    I can not modify lastvisit as it is updated every page view, across many pages, plus the script is encoded in parts which makes that more trouble.

    Is it possible to order by lastvisit but if they have a hiddentime to use the hiddentime instead of lastvisit time?

    Thanks

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    order by case when hiddentime = 0 then lastvisit else hiddentime end desc

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    alternatively,
    Code:
    ORDER BY COALESCE(NULLIF(hiddentime,0),lastvisit) DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wowowow. I'm impressed. I never imagined something like this would be possible without doing some long-winded query.

    Works perfectly.

    Is either method more "efficient"/less server intensive?

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Not significantly.

    Using method 1:
    10000 rows in set (0.01 sec)

    Using method 2:
    10000 rows in set (0.01 sec)

    An EXPLAIN on each query shows that no indexes can be used with either method even if indexes exist on hiddentime and lastvisit.


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
  •