SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2002
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select DISTINCT ordering issue

    I have a table of data:

    Table:

    TagID | Tag | LinkID | S
    6181 test 1483 A
    6180 test 1483 A
    6155 trading 73469 A
    6142 wire 73467 A
    6141 wire 73466 A
    6126 wire 73462 A
    6125 wire 73461 A
    6124 wire 73460 A
    6106 over 73459 A
    6105 pranks 73459 A
    6104 hill 73459 A
    6098 over 73458 A
    6095 hill 73458 A
    6092 jokes 73458 A

    And I'm trying to select the last 5 distinct LinkID's added to the table. I am using this query:
    select distinct LinkID from table where S = 'A' order by TagID desc limit 5

    But I am getting these results:
    73469
    73467
    73466
    73462
    73461

    What's missing is LinkID 1483. What am I doing wrong here?

  2. #2
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As you have specified that you wanted the 5 first results, it's not listed because it is further than that.

    Try to drop the "limit 5" statement, and you will see it.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by brsmu80 View Post
    What am I doing wrong here?
    you are selecting only LinkID but trying to order by TagID

    not sure how to work around this, because as is, it won't work

    what are you trying to do? what's this for?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Jan 2002
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you are selecting only LinkID but trying to order by TagID

    not sure how to work around this, because as is, it won't work

    what are you trying to do? what's this for?
    Thanks for the help guys.

    What this is is tags that are stored for an item(LinkID). I am trying to determine the last 5 items that have had tags added so I can review them.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    try this --
    Code:
    SELECT LinkID 
         , MAX(TagID) AS last_tag
      FROM table 
     WHERE S = 'A' 
    GROUP
        BY LinkID
    ORDER 
        BY last_tag DESC LIMIT 5
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Jan 2002
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Interesting, that works great. Thank you!

    Quote Originally Posted by r937 View Post
    try this --
    Code:
    SELECT LinkID 
         , MAX(TagID) AS last_tag
      FROM table 
     WHERE S = 'A' 
    GROUP
        BY LinkID
    ORDER 
        BY last_tag DESC LIMIT 5


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
  •