SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member BungeeBones's Avatar
    Join Date
    Jan 2008
    Location
    Florida
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can't retrieve correct data

    I am using two columns to select from. One is link_id and the other is timestamp. There are multiple entries of the same link and I want only the most recent posting each link in the list. I haven't been able to figure it out.

    Here is what I have so far

    SELECT DISTINCT `link_id` , `timestamp`
    FROM `price_slot_cancels`
    WHERE `timestamp` > ' 2012-06-16 00:00:00'
    AND `timestamp` < '2012-06-30 00:00:00'

    GROUP BY link_id
    ORDER BY `timestamp` DESC

    I've tried a bunch of different things and nothing is working

    Thanks in advance

    BungeeBones.com
    - A New Paradigm of Website Promotion

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT link_id 
         , MAX(`timestamp`) AS latest
      FROM price_slot_cancels
     WHERE `timestamp` > '2012-06-16 00:00:00'
       AND `timestamp` < '2012-06-30 00:00:00'
    GROUP 
        BY link_id
    ORDER 
        BY latest DESC
    note TIMESTAMP is a reserved word -- you really should rename that column
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member BungeeBones's Avatar
    Join Date
    Jan 2008
    Location
    Florida
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks! Awesome! and I took your advice and renamed the column

    BungeeBones.com
    - A New Paradigm of Website Promotion


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
  •