asked on

ADo select double numbers

I have a table with
Name, Number, Extention, Group
Eacht name has 2 extentions but the number is the same.

Now I need to selct one name from each Number. I am still getting double records as result.
I have
Jack, 3, 124, Marketing and I have Jack 1, 3, 123, Marketing
I need to select only one of these

My code is 
     SQL.Add('Select  distinct M_number, M_name, M_Extention, Group');
     SQL.Add('From Employees);
     SQL.Add('Where (Group = '+QuotedStr(Group)+')');
 
 
What is wrong in this?

Open in new window

Geert G

you are selecting multiple different ones with distinct not 1

and use parameters for performance, allways better !
and Group is a reserved word (bettter to use other column name)

as i dunno what database ... for oracle:
SELECT M_number, M_name, M_Extention, Group
FROM (SELECT M_number, M_name, M_Extention, Group, RANK() OVER (PARTITION BY M_Number ORDER BY M_Number) X
            WHERE GROUP = :AGRoup)
WHERE X = 1

Open in new window

Nayel

ASKER

I am using Access

Geert G

that's not a database ... it's a flat file :)

SELECT E.*
FROM Employees E,
(SELECT M_Number, Min(M_Name) XName FROM Employees Group By M_Number) X
WHERE E.M_Number = X.M_Number
AND E.M_Name = X.XName

Nayel

ASKER

No no.. I am using Delphi which is connected to Access. And the query should be written in an ADOQUERY

Geert G

just kidding

var AdoQuery: TAdoQuery;
begin
  AdoQuery.SQL.Text := 
    'SELECT E.* FROM Employees E,           '+
    '  (SELECT M_Number, Min(M_Name) XName  '+ 
    '   FROM Employees Group By M_Number) X '+
    'WHERE E.M_Number = X.M_Number          '+
    '  AND E.M_Name = X.XName               '+
    '  AND E.Group = :GROUP                 ';
  AdoQuery.Parameters.ParamByName('GROUP').Value := Group;
  AdoQuery.Open;

Open in new window

Nayel

ASKER

Getting a fault.

Geert G

and that would be ?

i tried to smell what it was, but i have a small cold ...

Nayel

ASKER

A syntaxerror in expression Min(M_Name) XName

ASKER CERTIFIED SOLUTION

Geert G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial