Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DISTINCT SQL statement

Status
Not open for further replies.
Feb 25, 2005
30
GB
Hi
I need to retrieve the first value from 2 columns that contain multiple values e.g

Act Name

200113 ALLIED BAKERIES BRADFORD
200113 ALLIED BAKERIES STOCKPORT
200116 SPEEDIBAKE
200116 SPEEDIBAKE BRADFORD
200116 SPEEDIBAKE WAKEFIELD
200117 SPARKS BAKERY

would give

Act Name

200113 ALLIED BAKERIES BRADFORD
200116 SPEEDIBAKE
200117 SPARKS BAKERY

My SQL (still very much a beginner here) is:

SELECT DISTINCT [custlist].[main act number], [custlist].[Customer Name]
FROM [custlist];

Any help would obviously be greatly appreciated.

kind regards
Rachel
 
Hi,

If the Bakery names are always ordered alphabetically for each account number then this works
Code:
SELECT [custlist].[main act number], Min([custlist].[Customer Name])
FROM [custlist] GROUP BY [custlist].[main act number];
Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations
 
hhmm thanks for the reply, unfortunately they aren't always in alphabetical order Harleyquinn. Can the statement be easily doctered to work around this ?

regards
Rachel
 
The only way I could think to do it then would be to have a primary key field (autonumber). Have a sub query to select the min() value of the primary key field of each act, and then the outer query to return the desired information. Something like:
Code:
SELECT [A].[Act], [custlist].[Customer Name] FROM [custlist],
(SELECT [custlist].[main act number] as Act, Min([custlist].[ID])as Minimum
FROM [custlist] GROUP BY [custlist].[main act number]) A
WHERE [A].[Minimum] = [custlist].[ID];
That would solve the problem.

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations
 
thanks very much Harleyquinn for your help I'll have a go now.

regards

Rachel
 
You are correct Harleyquinn (I am sorry for posting in 2 areas I wont do it again). And ref' the answers kindly received I'm trying hard to play around with the statements without asking very naive questions. So please forgive me ignorance.

regards
Rachel.
 
Have you tried Harleyquinn's first answer? Min([Customer Name]) will take the shortest name regardless of order. So even if your record was...

200116 SPEEDIBAKE WAKEFIELD
200116 SPEEDIBAKE
200116 SPEEDIBAKE BRADFORD

It will still take SPEEDIBAKE.

If you want SPEEDIBAKE WAKEFIELD instead, use First([Customer Name])
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top