SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    a question about news headlines order in MySQL

    Hello
    I have a MySQL table contains news headlines from many sources like:

    • BBC
    • CNN
    • CNBC
    • Other...

    Table structure:
    id = article ID
    source = like: CNN
    title = news headline
    sentTime = article time
    other...

    My question is:
    How can i order the headlines by news sources and recently added headlines.

    Example:

    Article 1 -> CNN -> 1 hour ago
    Article 2 -> BBC -> 2 hour ago
    Article 3 -> CNBC -> 3 hour ago
    etc...

    I want to order the result by the source then by the time.

    Can i do it via MySQL with one query?

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    ORDER BY FIELD(source, 'CNN', 'BBC', 'CNBC')

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does not work!!
    Code MySQL:
    ORDER BY FIELD(source, 1, 3)  LIMIT

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by web.designer.iq View Post
    Does not work!!
    what comes after LIMIT ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry
    It's working
    But how can i get just 5 record from each source and sort them successively

    example:

    LIMIT 5 from CNN
    LIMIT 5 from BBC
    LIMIT 5 from CNBC
    (then repeat the same)
    LIMIT 5 from CNN
    LIMIT 5 from BBC
    LIMIT 5 from CNBC
    etc...

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    that "repeat the same" business cannot be done with sql

    why not pull in the last 50 or so, regardless of source, and then play with them in your application code?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •