DISTINCT SQL statement

[shepshedlad]

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

 

[HarleyQuinn]

Hi,

If the Bakery names are always ordered alphabetically for each account number then this works

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

 

[shepshedlad]

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

 

[HarleyQuinn]

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:

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

 

[shepshedlad]

thanks very much Harleyquinn for your help I'll have a go now.

regards

Rachel

 

[HarleyQuinn]

No problem, let us know if it works for you

Harleyquinn

---------------------------------
For tsunami relief donations

 

[shepshedlad]

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.

 

[hkaing79]

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])