Re: distinct with all the columns

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
.

Relevant Pages

  • Re: duplicate occurance, assigning an ordinal value
    ... (there's no email, no snail mail, but somewhere should be gmail in my addy) ... rather an assignment of a unique value of where that duplicate occurs ... but that the first occurance of Model1234 would display ...
    (microsoft.public.excel.worksheet.functions)
  • Re: duplicate occurance, assigning an ordinal value
    ... Now it's a matter of configuring the formula and ... arranging the list. ... rather an assignment of a unique value of where that duplicate occurs ... but that the first occurance of Model1234 would display ...
    (microsoft.public.excel.worksheet.functions)
  • Query: Remove all but the first occurance
    ... Looking for a query that will remove any records, EXCEPT the first occurance ... where there are duplicate values in certain fields. ... Customer database. ...
    (microsoft.public.access.queries)
  • Re: duplicate occurance, assigning an ordinal value
    ... What I'm looking for more than a flag, ... rather an assignment of a unique value of where that duplicate occurs ... but that the first occurance of Model1234 would display ...
    (microsoft.public.excel.worksheet.functions)