Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4

Thread: how to do this

  1. #1
    Regular Coder
    Join Date
    Sep 2004
    Posts
    713
    Thanks
    6
    Thanked 2 Times in 2 Posts

    how to do this

    Hi

    i have a table `product` with structure like

    productid productname catid
    1 apple 1
    2 pear 1
    3 pork 2
    4 beef 2
    5 mutton 2
    6 cake 3
    7 bread 3


    how can i get only one record from each catid '1' , '2' , '3' either randomly or most new one? thanks
    flying dagger

  • #2
    Senior Coder nikos101's Avatar
    Join Date
    Dec 2006
    Location
    London
    Posts
    1,005
    Thanks
    58
    Thanked 10 Times in 10 Posts
    Code:
    SELECT DISTINCT column_name(s)
    FROM table_name


  • #3
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    if you want to find the newest one, I think you will need to have a field/column which has a time based value. Thiis could be the time it was added or some other relevant time.


    Make sure you use the ISO date ofrmat and that you format the column to be a date instaed of a 'varchar' or 'int' for example. Formatting the column correctly is vital if you want to be compare, relate or add/subtract dates

    bazz

  • #4
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    Quote Originally Posted by nikos101 View Post
    Code:
    SELECT DISTINCT column_name(s)
    FROM table_name
    This is not even helpful. It explains nothing.

    Code:
    SELECT productid, productname
    FROM
        products p
        ,(SELECT DISTINCT catid FROM products) cat
    WHERE
        p.catid = cat.catid
    This might work. It uses a subquery to produce a list of categories, then it finds one product for each category. If it doesn't work, it should give you something to play around with.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •