Re: distinct with all the columns

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


.

Relevant Pages

  • C# programmer looking for a job
    ... Software Development including Desktop, Client/Server and Database ... Practical skills in object oriented design and design patterns ... XML, Oracle, CVS, VSS, Delphi, bug tracking. ... Developed in Delphi5; ...
    (misc.immigration.usa)
  • Re: O/R Mapper
    ... | - create E/R model from niam model ... classes that contain, not only data, but also functionality as OO design is ... a database where they do not exist in the object model is corrupting the ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Date range on reports
    ... > box to your report with a control source like: ... >> In the Database window (Database window: The window that appears when you ... >> In the New Form dialog box, click Design View, and click OK. ... >> Begin by clicking Macro Names to display the Macro Name column. ...
    (microsoft.public.access.reports)
  • Re: limit of lines?
    ... And this is the fundamental mistake in your design. ... a objects from a database, this usually leads to very poor design. ... Framework in which all properties are backed, not by a simple field, but by ... we have major size projects that use the Delphi template ...
    (borland.public.delphi.non-technical)
  • Re: O/R Mapper
    ... > as OO design is not just about data in isolation. ... the proper tooling/methods are used which fit with the approach chosen. ... >> of the reality to come to the proper result. ... > constraints into a database where they do not exist in the object ...
    (microsoft.public.dotnet.languages.csharp)