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

 

[SuicidED]

SELECT DISTINCTROW Table2.Act, Min(Table2.Name) AS MinOfName
FROM Table2
GROUP BY Table2.Act
ORDER BY Table2.Act;

 

[PHV]

Why using DISTINCT or DISTINCTROW in an aggregate query ?
SELECT [main act number] AS Act, Min([Customer Name]) AS [Name]
FROM custlist
GROUP BY [main act number];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[shepshedlad]

Cheers PHV for your help I appreciate it.

regards
Rachel

 

[HarleyQuinn]

Rachel,

Is it correct to assume that PHV's answer couldn't work for you in the same way the exact same answer couldn't work for you in thread702-1025564??

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.

 

[HarleyQuinn]

Sorry, I meant no offence by my post. I just wanted to clear it up in my mind and incase someone else found the thread while searching so they knew why answers provided may/may not have worked for your scenario.

Harleyquinn

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

 

[PHV]

Another (not so reliable) way:
SELECT [main act number] AS Act, First([Customer Name]) AS [Name]
FROM custlist
GROUP BY [main act number];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244