SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist
    Join Date
    Apr 2003
    Location
    North Carolina, USA
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trying to get rid of certain rows here...

    OK, say I have a result table that looks like this:

    Code:
    1 Item    1084985159 Test 
    3 Item3  1084984893  Test2
    2 Item2  1084984864  Test3
    2 Item2  1084984849  Test4
    1 Item    1084984821 Test5
    See the column that contains 'Item<something>' (Like Item or Item3, etc.)? What I want to do is only get the first occurrence of any item and just get that row. After that, I don't want to get any more rows that contain that same item value. I tried messing with DISTINCT but couldn't seem to get that to work. Basically, I'm trying to get it to return this instead:

    Code:
    1 Item    1084985159 Test 
    3 Item3  1084984893  Test2
    2 Item2  1084984864  Test3
    My query contains a bunch of LEFT JOIN's (there's actually a lot more data than this from different tables). I just want 1 row for each 'Item<something>' entry.

    Thanks for any assistance.

  2. #2
    This is all Mumbo Jumbo Amit's Avatar
    Join Date
    Oct 2001
    Location
    New Delhi, India
    Posts
    277
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this ...

    Code:
    SELECT DISTINCT item_field, number_field, test_field FROM table WHERE item_field LIKE 'Item%' GROUP BY item_field
    HTH!

  3. #3
    SitePoint Evangelist
    Join Date
    Apr 2003
    Location
    North Carolina, USA
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I guess I didn't describe the problem good enough. What I'm trying to say is that I have all of these left joins and all of this data in these tables. I'm trying to pick out the greatest number out of this one field in one of the joined tables and then delete results which contains a duplicate entry for one of the fields in the entire query. When I try to select that field using DISTINCT, it doesn't work. I think it has to do with the fact that I'm using the GROUP BY clause at the end of all of these joins (since I'm using MAX()). Any help appreciated.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    what do you mean by "all of these left joins" and "delete results"?
    r937.com | rudy.ca | 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
  •