SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Always learning viveknarula's Avatar
    Join Date
    Mar 2006
    Location
    INDIA
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question selecting products which are latest and with price sort

    Hi,

    I am trying to fetch 12 products which are latest and sorting them for price. However not able to get the results i needed.

    Here is the SQL query.
    Code:
    select p.products_id, p.products_image, p.products_tax_class_id, pd.products_name, if(s.status, s.specials_new_products_price, p.products_price) as products_price from products p left join specials s on p.products_id = s.products_id, products_description pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' order by products_price asc, p.products_date_added desc limit 12
    if i put only order by price then it gives some other products and if i put by date it gives some other products. However i want to get the products which are lastest and among them by sorting with price (min to max) OR (max to min)

    How can i achieve this please ?

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    use this --
    Code:
    ORDER BY p.products_date_added DESC LIMIT 12
    retrieve the 12 rows, and sort them in an array in your application language (php or whatever you're using)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Always learning viveknarula's Avatar
    Join Date
    Mar 2006
    Location
    INDIA
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    use this --
    Code:
    ORDER BY p.products_date_added DESC LIMIT 12
    retrieve the 12 rows, and sort them in an array in your application language (php or whatever you're using)
    so there is no way to have both with one sql query ?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you can try this ...
    Code:
    SELECT * FROM (
    select p.products_id, p.products_image, p.products_tax_class_id, pd.products_name, if(s.status, s.specials_new_products_price, p.products_price) as products_price from products p left join specials s on p.products_id = s.products_id, products_description pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' order by p.products_date_added desc limit 12) AS your_original_query
    ORDER BY products_price ASC
    but using LIMIT in a subquery doesn't work in any other database besides mysql and even then, not in every mysql version, although if you're on version 5 you should be okay
    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
  •