Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Unanswered: dublicate records

    I am trying to display records based on item_id and discription. But problem is, one item_id has more than one Discriptions but I want to display only one item_id with its discription. Distinct is not working because each record is different. Can I get only one record for each item? table structure is

    item_no int,
    desc varchar(80)

    Any help will be highly appreciated.
    mr_roomi

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You will need some sort of business logic to decide which description you want to display when multiple descriptions are available. First alphabetically? Longest? Last entered?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    business logic

    I need first description.

    thanks for prompt reply
    mr_roomi

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT item_no, MIN(Desc) AS MIN_Desc
    FROM yourTable99
    WHERE intem+no = @item_no
    GROUP BY item_no
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First description?

    First how?

    Alphabetically (as Brett's example)?

    First entered?

    First among equals?

    Women and children first?

    There is no "first" description, except as defined by you.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Thumbs up Thanks

    Thanks Brett, it worked
    mr_roomi

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    First description?

    First how?

    Alphabetically (as Brett's example)?

    First entered?

    First among equals?

    Women and children first?

    There is no "first" description, except as defined by you.

    Don't you just hate Access
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Nah. FIRST() and LAST() are useful functions. But lately I've been jumping back and forth between Access, SQL Server, and Oracle, sometimes several times each day, and it always takes a minute or two to change hats. That is a real pain.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, so how does Access know what FIRST() and LAST() is?

    I should profiler that ans see what it does....

    Betcha it returns all the rows of the result set, and in it's own convoluted way, determines by the order the result set comes back in...even tjough it could be different every time...

    Betcha
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think it is like the TOP clause in SQL, where it uses the order defined by the query. If no order supplied, than all bets are off, of course.

    By the way, I tried using ROWNUM in Oracle to return the first 10 rows of an ordered query, like TOP in SQL. No go. The ROWNUM value is applied BEFORE the sort! Had to enclose the sorted set in a subquery and then wrap it in a shell for selecting based on ROWNUM. Sucks.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Every Row in Oracle gets enumerated, so it is an actual "pointer" to that specific row...Oracle is all about pointers to the data...

    Have you used the reference cursors yet?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah. Had to.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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