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.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?|||I need first description.

thanks for prompt reply|||SELECT item_no, MIN(Desc) AS MIN_Desc
FROM yourTable99
WHERE intem+no = @.item_no
GROUP BY item_no|||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.|||Thanks Brett, it worked|||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|||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.|||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|||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.|||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?|||Yeah. Had to.