Show distinct value based on one field

[tmyers]

I am having a slight problem with one of my queries. It is just a "history" query that shows all quotes starting from yesterday into the past. The problem is that a quote number can exist multiple times (the JobID however is still unique to each one). When a quote number shows up multiple times, there are different "packages" for that quote. For the purposes of this query however, I need to only show it once. How can I get that to happen?

As you can see, one is showing up twice. Can I somehow drill it down to only show a given quote number once?
Here is the current SQL behind the query:

SELECT DISTINCT First(tblJobDetails.QuoteNum) AS FirstOfQuoteNum, tblJobDetails.JobName, tblJobDetails.BidDate, tblUsers.FirstName, tblJobDetails.CompletedDate, tblJobDetails.JobAwarded, tblJobDetails.JobLost
FROM tblUsers INNER JOIN tblJobDetails ON tblUsers.UserID = tblJobDetails.UserID
GROUP BY tblJobDetails.JobName, tblJobDetails.BidDate, tblUsers.FirstName, tblJobDetails.CompletedDate, tblJobDetails.JobAwarded, tblJobDetails.JobLost
HAVING (((tblJobDetails.BidDate)<Date()));

 

[Isaac]

Maybe if you only group by the two fields that duplicate?

 

[jdraw]

When a quote number shows up multiple times, there are different "packages" for that quote.

Sounds like there is some other factor involved -that is, quote number is not unique.
For a list of unique QuoteNum and JobName you could try

SELECT DISTINCT QuoteNum  & JobName
FROM tblJobDetails

 

[Gasman]

You are not showing the relevant picture?
I suspect also that your group on every field is causing the problem?

 

[tmyers]

Maybe if you only group by the two fields that duplicate?

Doing that seemed to correct it.

 

[Isaac]

Doing that seemed to correct it.

Glad to hear it's working !!

 

[Isaac]

Pat, maybe we're just not on the same page yet I refer to one thing (not well explained at all, I admit) and you refer to another.

If you group by only 2 of the fields, and then use a random function like 'first' on the other two (say 4 total), you may get the result you want.
In this case the values will all come out correctly - but this won't necessarily work correctly for all data, just some.

Attached is an example where qry2 "gets rid of" the extra "duplicate" value (duplicate as in, the first 2 fields are dupes).

 

[Isaac]

Did you look at my uploaded sample? It works quite well and does not cause an error.

Note that I did not merely say, only aggregate by 2 columns. I said specifically only group by two columns.

That means either group by or some function

Right - either one or the other. Group by the first two, use a function on the 2nd two, as my uploaded sample demonstrates.

 

[Isaac]

you can't just group by two of them.

Well, but you can. You can just group-by two of them, and aggregate the rest. But I see what you mean.

 

[Isaac]

You have to aggregate all of them either by grouping or using a function.

"by grouping or using a function".

Group on 2, use a function on the rest.

Aggregation is the function performed on a group.

Grouping is not aggregating. Some columns are grouped. Other, different, columns, are aggregated within the group.

 

[Isaac]

When you create a query involving these techniques, some columns are in Group By, and aggregate functions are being performed on others.
There is no cross over. Period.

We are talking about database queries, not Merriam Webster.

 

[ebs17]

An approach that usually works much better: calculate first, then join.
Calculation:

SELECT
   MIN(JobID) AS MinJobID
FROM
   tblJobDetails
WHERE
   BidDate < Date()
GROUP BY
   QuoteNum

It is possible that more fields have to be grouped. In any case, it is filtered immediately and then the smallest JobID per group is determined. In order to get all fields of the table, the table is joined again. The tblUser table can now also be included. Alltogether:

SELECT
   JD.QuoteNum,
   JD.JobName,
   JD.BidDate,
   U.FirstName,
   JD.CompletedDate,
   JD.JobAwarded,
   JD.JobLost
FROM
   tblUsers AS U
      INNER JOIN
         (
            (
               SELECT
                  J.*
               FROM
                  (
                     SELECT
                        MIN(JobID) AS MinJobID
                     FROM
                        tblJobDetails
                     WHERE
                        BidDate < Date()
                     GROUP BY
                        QuoteNum
                  ) AS S
                     INNER JOIN tblJobDetails AS J
                     ON S.MinJobID = J.JobID) AS JD
         )
      ON U.UserID = JD.UserID

This query is more efficient than a grouping orgy over all fields.