Using Select Distinct with *

[Guest]

I am using Office 2003 on Windows XP.

The following SQL string will create a new table containing a unique list of
account distributions only:

SELECT DISTINCT Account_Distribution INTO [UNIQUE] FROM [COMBINED]

How can I alter this SQL to get that same unique list of account
distributions, but also all the other field contents for each record rather
than ONLY the account distribution? Can I not use "*" somehow to get at it or
do I have to list every field (30 or so) I want?

Could someone please correct my SQL to accomplish this efficiently? Thanks
much in advance.

 

[Douglas J. Steele]

Realistically, you shouldn't have a situation where SELECT DISTINCT * makes
sense: every row should have a primary key on it, and since you can't repeat
primary keys, that means that SELECT DISTINCT * would be the same as SELECT
*.

 

[Michel Walsh]

Hi,


You can't use * with DISTINCT neither if you have a GROUP BY clause, or an
aggregate. It is expressively enforced by the syntax parser.


You can easily get all the fields of a table (or query used as table) in the
grid, individually named, by double-clicking on the graphical representation
of the table in the designer, and dragging the "selection" of all its fields
into the grid.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks, but my code isn't running in Access.

I guess I'll develop a work around, like creating a table of unique accounts
first, then...

Hi,


You can't use * with DISTINCT neither if you have a GROUP BY clause, or an
aggregate. It is expressively enforced by the syntax parser.


You can easily get all the fields of a table (or query used as table) in the
grid, individually named, by double-clicking on the graphical representation
of the table in the designer, and dragging the "selection" of all its fields
into the grid.


Hoping it may help,
Vanderghast, Access MVP

I am using Office 2003 on Windows XP.

The following SQL string will create a new table containing a unique list
of
account distributions only:

SELECT DISTINCT Account_Distribution INTO [UNIQUE] FROM [COMBINED]

How can I alter this SQL to get that same unique list of account
distributions, but also all the other field contents for each record
rather
than ONLY the account distribution? Can I not use "*" somehow to get at it
or
do I have to list every field (30 or so) I want?

Could someone please correct my SQL to accomplish this efficiently? Thanks
much in advance.

 

[Michel Walsh]

Hi,


There is no workaround in most SQL dialects, you have to list all the
fields. Can't you generate the SQL code in Access, even if it is not running
in Access, in the end. It is just a matter to generate the code.

30 fields is a lot of fields. Do you start from a normalized design? If so,
I assume your 30 fields are generated through a Crosstab, so you already
have an implicit DISTINCT through the GROUP BY of your Crosstab. If you
don't come from a normalized table , may be making such a table *is* the
work around (if we can speak of a work around in that case) you are looking
for.


Hoping it may help,
Vanderghast, Access MVP

Thanks, but my code isn't running in Access.

I guess I'll develop a work around, like creating a table of unique
accounts
first, then...

Hi,


You can't use * with DISTINCT neither if you have a GROUP BY clause, or
an
aggregate. It is expressively enforced by the syntax parser.


You can easily get all the fields of a table (or query used as table) in
the
grid, individually named, by double-clicking on the graphical
representation
of the table in the designer, and dragging the "selection" of all its
fields
into the grid.


Hoping it may help,
Vanderghast, Access MVP

I am using Office 2003 on Windows XP.

The following SQL string will create a new table containing a unique
list
of
account distributions only:

SELECT DISTINCT Account_Distribution INTO [UNIQUE] FROM [COMBINED]

How can I alter this SQL to get that same unique list of account
distributions, but also all the other field contents for each record
rather
than ONLY the account distribution? Can I not use "*" somehow to get at
it
or
do I have to list every field (30 or so) I want?

Could someone please correct my SQL to accomplish this efficiently?
Thanks
much in advance.