Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2009
    Posts
    10

    Unanswered: how to get record with same field

    I have a table
    Column 1: Id
    Column 2: Image
    Column 3: IdProduct

    like this
    0 | pic1 | 1
    1 | pic2 | 1
    2 | pic3 | 2
    3 | pic4 | 3
    4 | pic5 | 4
    5 | pic6 | 4
    6 | pic7 | 4
    7 | pic8 | 4
    8 | pic9 | 5
    I want to query first record with same IdProduct
    result:
    0 | pic1 | 1
    2 | pic3 | 2
    3 | pic4 | 3
    4 | pic5 | 4
    8 | pic9 | 5

    help me. thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Version of SQL Server please sir.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2009
    Posts
    10
    sql server 2005
    thanks for your reply

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT          *
    FROM             --windowised data
                              (
                                          SELECT            id
                                                         , image
                                                         , idproduct
                                                         , magic             = ROW_NUMBER()   OVER  (PARTITION BY        idproduct
                                                                                                       ORDER BY                 id)
                                          FROM             datable
                              ) AS windowised_data
    WHERE           magic = 1
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2009
    Posts
    10
    I don't undestand anything. hihi
    but thanks a lot. It works fine

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That'll be the magic
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what's-------- with-------- the-------- weird-------- spacing?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    what's-------- with-------- the-------- weird-------- spacing?
    I wasn't on my normal lappy, so used Word for formatting. It barfed it up even worse than my other text editors. I *still* don't know how to copy and paste into this damned forum whilst preserving formatting properly.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Mar 2009
    Posts
    4
    Quote Originally Posted by pootle flump
    Code:
    SELECT          *
    FROM             --windowised data
                              (
                                          SELECT            id
                                                         , image
                                                         , idproduct
                                                         , magic             = ROW_NUMBER()   OVER  (PARTITION BY        idproduct
                                                                                                       ORDER BY                 id)
                                          FROM             datable
                              ) AS windowised_data
    WHERE           magic = 1
    wow ... that's an impressive code..
    I could only think the grouping :

    SELECT min(id), min(image), idproduct
    group by IdProduct

    thanks for sharing the "magic of partition", I really like it.

  10. #10
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    select a.* from datable a join
    (select min(id)id,IdProduct from datable group by IdProduct)b
    on a.id=b.id

  11. #11
    Join Date
    Mar 2009
    Posts
    10
    thanks all of you.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    I wasn't on my normal lappy, so used Word for formatting. It barfed it up even worse than my other text editors. I *still* don't know how to copy and paste into this damned forum whilst preserving formatting properly.
    Whatever UI you use for writing your code, change the settings so that tabs are inserted as spaces.

    bingo
    George
    Home | Blog

Posting Permissions

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