Re: distinct with all the columns

Looks good, but considering that it had to go so quick, we have used
Integration services to cleanse the data added some calculatons. This has
fixed the problem :-)

Thanks anyway :-)

"Yong Zou (SearchIgnite)" wrote:

Is the total amount of the order always has bigest brpr within that order?
If it does, you can try following

select distinct ordnr,
(select max(brpr) from Order where Ordnr = or.Ordnr and magnr =
or.magnr and date = or.date) as brpr,
magnr,
date
from Order or

"Philippe" <Philippe@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C0D3F28F-2424-42B7-A9F6-C46D81530962@xxxxxxxxxxxxxxxx
Thanks, but changing the database schema is not an option because it's an
ERP
system based on PROGRESS.

We are working on another point of view, maybe this will help to get this
stupid data to come out of this PROGRESS DB.

thank for your suggestions...

P
"Arnie Rowland" wrote:

You may have a table design that will not allow you to retreive the
information you desire.

In your situation (if the table has only the four columns you
indicated),
you have absolutely no way to discern which row is the 'first' row. And
the
database couldn't care less about which row is first -by design.

I suggest you revisit the table design. As a ridiculous kludge you could
add
another column (or two or three) to indicate row sequence for the order.

I recommend that you examine how orders are handled in the Northwind
database (the sample test and learning database that comes with SQL
Server
and Access.)

A more workable design is one where there is a Order header table (one
row
per order). That table contains the order number, date, customer id,
etc.

And then there is a OrderDetails table that contains the item by item
information. It is 'related' to the Orders table with a Foreign key -
Primary key relationship.

So, if there are only the four columns you indicated, exactly where is
the
'total amount of the order' placed?
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam


"Philippe" <Philippe@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2536268E-DF2D-4217-9E99-6FF50E5D4205@xxxxxxxxxxxxxxxx
I tried this, but it didn't worked out.

This is my table:

ordnr, brpr, magnr,date,

The table contains orders, the ordnr contains the number of the order
but
it
is not unique because an order consists out of more then one line, the
total
amount of the order is calculated and put in the first occurance (thus
the
first line) of an order with a specific number.

eg: ordernumber 123 consists of 3 records like this:
123 125 1 01/01/2001 (*)
123 100 1 01/01/2001
123 25 1 01/01/2001

I want only the first occurance of that records out of my database in
this
case the * row. We tried everything with distinct and other
possibilities
but
I'm afraid this isn't possible, or is it ?

thanx

"Arnie Rowland" wrote:

Without having your table schema DDL, and some sample data, it is
difficult to 'guess' the nature of your issue.

However, using my weegee board -yes, that is how My weegee board is
spelled!

You could use GROUP BY. (A bit heavy-handed, but hey, it works!)

SELECT
Column1
, Column2
, Column3
, Column4
FROM MyTable
WHERE (Criteria here)
GROUP BY
Column1
, Column2
, Column3
, Column4

If Column4 has multiple entries (all others being the same) there
will be
only 1 row in the resultset. The same goes for any combinations,
starting
from the rightmost listed column, where the columns have duplicate
values.

This should work, and there may be other suggestions coming
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam


"Philippe" <Philippe@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:39341A08-7912-4DB0-85FD-940F51BD8C40@xxxxxxxxxxxxxxxx
Hi,

I've got something, but I can't get all of my columns shown. I've
got a
table with a few columns and in one columns there are records with
duplicate
values, so I use a distinct to get all the duplicates out of the
result
set,
but this only works for 1 column, I also want to show the other
columns
of
that record that was filtered by the distinct clause, but this
doesn't
work.

Any suggestions?

thanks






.