SQL: Select DISTINCT only on certain fields?

[Newsgroup_User]

This is an odd question. Partly because it's late and I'm not thinking
straight, but...

I have a query consisting of 9 joins. It works fine.

However, I've encountered a bit of an issue with my data that would much
easier to fix via a query than actually fixing the data (as the data fix
would require committee approvals and the like...)

In a nutshell, my query is trying to return a DISTINCT set of recrods.

The catch is that my records are ALMOST distrinct, except for one field. Is
there a way to use the distinct command in a way the excludes specific
fields?

-Darrel

[Newsgroup_User]

"darrel" <notreal@nowhere.com> wrote in message
news:enesjr$mc2$1@forums.macromedia.com...
> The catch is that my records are ALMOST distrinct, except for one field.
> Is there a way to use the distinct command in a way the excludes specific
> fields?
>
> -Darrel

No; DISTINCT applies to all columns returned by your SELECT statement.
You didn't provide a lot of information, but typically, if you need
DISTINCT, then you've done something wrong.

Giving you the benefit of the doubt, if you need some of your returned
columns to be unique in the result set, but not all of them, then you need
to use GROUP BY. You'll need to decide how you want SQL Server to determine
which value to use for columns that are not part of the unique group. If
they're all identical, then you can use any aggregate function that supports
the data type (MAX and MIN are usually safe since they work on both
character and numeric data). If not, your business rules will dictate which
one to pick.

[Newsgroup_User]

"darrel" <notreal@nowhere.com> wrote in message
news:enesjr$mc2$1@forums.macromedia.com...
> The catch is that my records are ALMOST distrinct, except for one field.
> Is there a way to use the distinct command in a way the excludes specific
> fields?
>
> -Darrel

No; DISTINCT applies to all columns returned by your SELECT statement.
You didn't provide a lot of information, but typically, if you need
DISTINCT, then you've done something wrong.

Giving you the benefit of the doubt, if you need some of your returned
columns to be unique in the result set, but not all of them, then you need
to use GROUP BY. You'll need to decide how you want SQL Server to determine
which value to use for columns that are not part of the unique group. If
they're all identical, then you can use any aggregate function that supports
the data type (MAX and MIN are usually safe since they work on both
character and numeric data). If not, your business rules will dictate which
one to pick.

[Newsgroup_User]

> No; DISTINCT applies to all columns returned by your SELECT statement.
> You didn't provide a lot of information, but typically, if you need
> DISTINCT, then you've done something wrong.

It's a complex join. The data is this:

Packet table (record of a packet of forms)

which point to...

Packet variations (each packet can have a variation)

which point to...

Forms table (record of a form)

which points to...

Form variations (individual forms)

The catch is that multiple Packet Variations can point to the same Forms, so
when I query the database to get all variations of a packet and related
forms, I can end up with duplicate records of forms.

This isn't a big deal, as DISTINCT can work for this. The catch is that we
also let each Packet Variations' forms be sorted individually. It's this
'sort' field that ends up causing the Distinct not to work, as the sort
might be completely unique for each one.

ALL THAT SAID, this is definitely more of a policy issue than a technology
issue. However, getting them to change policy is sometimes more work than
finding a technology workaround.

I could just query the DB and return all the duplicates and then sort things
out in the code, but I was hoping that maybe there was a clever way to
handle this via SQL.

> Giving you the benefit of the doubt, if you need some of your returned
> columns to be unique in the result set, but not all of them, then you need
> to use GROUP BY. You'll need to decide how you want SQL Server to
> determine which value to use for columns that are not part of the unique
> group.

Ah, I'll look into that! Thanks, lionstone!

-Darrel

[Newsgroup_User]

"darrel" <notreal@nowhere.com> wrote in message
news:enesjr$mc2$1@forums.macromedia.com...
> The catch is that my records are ALMOST distrinct, except for one field.
> Is there a way to use the distinct command in a way the excludes specific
> fields?
>
> -Darrel

No; DISTINCT applies to all columns returned by your SELECT statement.
You didn't provide a lot of information, but typically, if you need
DISTINCT, then you've done something wrong.

Giving you the benefit of the doubt, if you need some of your returned
columns to be unique in the result set, but not all of them, then you need
to use GROUP BY. You'll need to decide how you want SQL Server to determine
which value to use for columns that are not part of the unique group. If
they're all identical, then you can use any aggregate function that supports
the data type (MAX and MIN are usually safe since they work on both
character and numeric data). If not, your business rules will dictate which
one to pick.

[Newsgroup_User]

"darrel" <notreal@nowhere.com> wrote in message
news:englqn$q8a$1@forums.macromedia.com...

> Ah, I'll look into that! Thanks, lionstone!
>
> -Darrel

In the situation you described where you have records that contain
collections (conceptually speaking), the Data Shaping provider can be a real
asset. It lets you return "shaped recordsets" that have a recordset inside
a record; it also lets you compute aggregates from the source data, but
that's for another day. Look to www.4guysfromrolla.com for a good
introduction to data shaping.