Please help with DISTINCT variation query..Thanks! 1

[Taran72]

I am sure I have seen an applicable solution to my problem, but I cannot find it in the threads. Here's an example of my database:

Name1 Name2 Path
----- ----- ----
Bob Reel \directory\bob.jpg
Bob Hill \directory\bob.jpg
Fred Sadd \directory\fred.jpg
Fred Pudd \directory\fred.jpg
Fred Yumm \directory\fred.jpg
Donna Dune \directory\Donna.jpg
Donna Acre \directory\Donna.jpg
Donna Wind \directory\Donna.jpg

I need a query that will group the rows on the FIRST INSTANCE of the PATH column to the TOP of the database.

Example:

Name1 Name2 Path
----- ----- ----
Bob Reel \directory\bob.jpg
Fred Sadd \directory\fred.jpg
Donna Dune \directory\Donna.jpg
...rest of db

The main problem is that ALL the rows are distinct. It would also be an acceptable result if ONLY the first instances were shown...than I can just paste/replace the results into the previous database.

Thank you...this site and it's contributors are impressive.

Dan

 

[cmmrfrds]

You could give this a try. I assume you mean table for where the data resides. This uses a subquery to get only the top name2 and aliases to keep the tables straight. If you need a particular order then add order by.

Select Y.Name1, Y.Path,
(Select top 1 Name2 from yourtable X
where X.Name1 = Y.Name1 and X.Path = Y.Path) as name2
from yourtable Y
group by Name1, Path

 

[Taran72]

That did not seem to work. It asks me for the value of "name2", which I assume it means that it is not accepting the "AS" statement. Hmmm...thanks for your help.

Dan

 

[BoxHead]

Dan,

If you have a field in your table called "Name2", you won't be able to use that as an alias.

I'm still getting used to the functionality of the Totals choices in queries but this might be a simple solution for you.

In QBE Grid view, select View > Totals from the menu to show the Totals row on the grid.

In the Path column, select FIRST from the Totals dropdown.

Let me know how it turns out.

If that doesn't work, try the distinct values property in the query properties pane. You may have to drag the path column all the way to the left for this to work.

Sorry, I don't have time to check these out before posting, but it's a busy night.


HTH John

Use what you have,
Learn what you can,
Create what you need.

 

[BoxHead]

Dan,

I guess it's never too busy...

I was close with the "First" idea, but I had it reveresed.

Don't select first for the PathName. Select first from the Totals Row for the other fields in the query.

HTH John

Use what you have,
Learn what you can,
Create what you need.

 

[Taran72]

Thank you for your help...this site has the quickest and highest quality responses anywhere BY FAR. People here really understand the significance of an online community.

Dan