Solved

SQL problem

Posted on 2004-10-19
21
150 Views
Last Modified: 2010-04-05
I use next:

sql := 'select distinct N2 from tbc.dbf where N2<>'''' ';

How can I read N1 for first distinct N2?

When I use sql := 'select distinct N2 from tbc.dbf where N2<>'''' '; I get 974 results.
For sql := 'select distinct N1,N2 from tbc.dbf where N2<>'''' '; get 1060 !?

I have for same N2 differents N1. I need to have just the first N1 for first found N2.

Can I do this into a single SQL ?
0
Comment
Question by:ginsonic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
  • +4
21 Comments
 

Expert Comment

by:jonas78
ID: 12350813
Have you tried something like this:

select N1, N2
where N2 <> ''''
and N2 = N1
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 12351042
Let distinct affects N2:
sql := 'select distinct N2,N1 from tbc.dbf where N2<>'''' ';
just from head, not tested ....
0
 

Expert Comment

by:jonas78
ID: 12351208
Another (perhaps) possible solution... also from head so you might want to fiddle around abit with it :-)

Q1 := TQuery.Create(self);
with Q1 do
  try
    Screen.Cursor := crSQLWait;

      SQL.Add('SELECT N1, N2');
      SQL.Add('FROM tbc.dbf');
      SQL.Add('WHERE N2<>'''' ');
      SQL.Add('AND N1 = N2 ');
      Prepare;
      Open;
      First;

     //Save the result to any String variable
     Temp :=  FieldByName('N1').AsString;    
   
  finally
    Screen.Cursor := crDefault;
    Close;
    Free;
  end;
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 45

Expert Comment

by:aikimark
ID: 12352218
Depending on your database engine:

select N2, First(N1)
from tbc.dbf
where N2<>""
Group By N2
0
 
LVL 9

Author Comment

by:ginsonic
ID: 12354891
N1<>N2. N1 is a name, N2 is a code.
0
 
LVL 9

Author Comment

by:ginsonic
ID: 12354905
First(N1) not supported by my db engine.
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12355700
What database is it ?
0
 
LVL 17

Expert Comment

by:geobul
ID: 12355733
Hi,

sql := 'select N2, N1 from tbc.dbf where N2<>'''' ';

should be enough. The first record in this dataset is the same as in your dataset.

Regards, Geo
0
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 12356972
>>> sql := 'select distinct N2 from tbc.dbf where N2<>'''' ';

>>> How can I read N1 for first distinct N2?

Ok let me get this straight, N2 and N1 are fields in the same table
If you only want distinct N2 where N2 <> '' but you also want the associated N1 value then you could sort of cheat
Give the table an alias and link back on itself

select
  distinct N2,
  (select TOP 1 N1 from tbc.dbf where N2 = DBF.N2) as N1
from
  tbc.dbf DBF
where
  N2 <> ''
order by
  N2

0
 
LVL 45

Expert Comment

by:aikimark
ID: 12357005
I think we will need some more information on this...
1. what database engine ARE you using?
2. why are you using Distinct?  (the rows returned are sorted)
3. what is the relationship between N2 values and N1 values?

====================
A more efficient version of Geo's comment (if your database engine supports it):
Select Top 1 N2, N1
From tbc.dbf
Where N2<>""
0
 
LVL 45

Expert Comment

by:aikimark
ID: 12357186
mikelittlewood,

Your SQL is promising.  I offer some comments:
1. Since "distinct" orders the rows returned, the "order by" clause is redundant
2a. When aliasing tables, it doesn't make sense to alias them with the full table name.  Rather, an abbreviation or use reference is more common.
2b. Using a table alias that is the same as the table name can cause SQL confusion and syntax errors.
3. Your comment that the table was "linked back on itself" isn't quite an accurate description of your SQL example.  You have written a correlated sub-query as a column definition.

Suggested alternative:
Select
  Distinct O.N2,
  (Select TOP 1 C.N1 From tbc.dbf As C Where C.N2 = O.N2) As N1
From
  tbc.dbf As O
Where
  O.N2 <> ''

Note: The "As" keyword may not be required by your database engine's SQL.
0
 
LVL 17

Expert Comment

by:geobul
ID: 12357475
Well, since DISTINCT orders the returned set then my previous comment is wrong. Perhaps it should be:

sql := 'select N2, N1 from tbc.dbf where N2<>'''' order by N2';

BTW I'm not sure that a set returned by
sql := 'select distinct N2 from tbc.dbf where N2<>'''' ';
keeps any relationship with the original table in terms of record numbers (speaking about dbf and BDE). Actually I'm sure because CanModify property of the query is false.

Regards, Geo
0
 
LVL 45

Expert Comment

by:aikimark
ID: 12357714
Geo,

The "record number" should be a datum in one of the columns, not a relative record number in the returned recordset.

There doesn't appear to be any requirement to update this recordset, just get the N2,N1 values.  You are correct in your observation that the use of the Distinct keyword (and other SQL configurations) will result in a non-updatable recordset.
0
 
LVL 17

Expert Comment

by:geobul
ID: 12357844
aikimark,

Exactly. Which simply means that there is no such thing like N1 value for the first record of the returned dataset because there is no such record. Am I wrong?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 12357930
Geo,

If we were to use
Select Distinct N2, N1 ...

Then both columns would be sorted.  You are correct.

============
There is a bit of ambiguity in the question, depending on how we interpret the use of "first found".  Thus my question about the use of the Distinct clause.
0
 
LVL 9

Author Comment

by:ginsonic
ID: 12358243
To answer :
1. I use a simple Fox dbf table ( the program is a simple validation application ).
2. N1 is my pacient name and the N2 is the unique number ( in my country is similar with social code ).

I receive some reports where I have same N2 ( unique code ) but with different N1 ( names ). The reason is some typing erors.

Example:

  192347872  John Doe
  192347872  Jhn Doe
  192347872  Jo hn Doe.

Using N2 ( this code is unique for me patient ) I wish to know the real number of patients and the name.
With top sql script I can find the real number of patients, but I wish to have a name too, not just a code.

Example:
  192347872 John Doe ( or Jhn Doe or Jo hn Doe, don't important for me but need a name. Right typed or not )
0
 
LVL 45

Expert Comment

by:aikimark
ID: 12359084
>>I wish to know the real number of patients and the name.

Let's try something like this:
Select N2, Count(N2), (Select TOP 1 C.N1 From tbc.dbf As C Where C.N2 = O.N2) As PersonName
From
  tbc.dbf As O
Where
  O.N2 <> ''
Group By N2
0
 
LVL 45

Expert Comment

by:aikimark
ID: 12359206
oops...forgot to name a column

Select N2, Count(N2) As PersonCount, (Select TOP 1 C.N1 From tbc.dbf As C Where C.N2 = O.N2) As PersonName
From
  tbc.dbf As O
Where
  O.N2 <> ''
Group By N2
0
 

Expert Comment

by:jonas78
ID: 12359335
Alot of things got cleared up, but Im still a bit confused.
By not putting in any more specific rules in the SQL query you are bound to get more then one result from the query (if you have more then one records in your db)
So my question is how/when do you read the result from the query?

Some comments on your sql:

1. sql := 'select distinct N2 from tbc.dbf where N2<>'''' '; I get 974 results.
Comment: This tells me that you have a total of 974 records with unique N2 number

2. sql := 'select distinct N1,N2 from tbc.dbf where N2<>'''' '; get 1060 !?
Comment: This tells me that you have a total of 1060 records with different N1 names

3. If we sum this up, we get that you have at least  1060 - 974 = 86 records with duplicate N2 (based on your previous post)
    So there is nothing strange with your two queries.

I don't se the problem here, but if you could provide some code on where you read the result of the query I/we might be able to help out a bit more
0
 

Expert Comment

by:jonas78
ID: 12359815
My solution to the problem would be:
1. Open the database and remove the N2 record duplicates
   (Just order the database by N2, and then just scroll down until you see two or more of the same N2 number. This should not take more then 15 minutes or so)
2. Add the necessary code to the program so not 2 of the same N2 number can exists in the database.
   (You probably have a option to set one field in the database as a "Key" field which means that no duplicates can exist )

0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 12360080
you can easily see the duplicate ID/Name pairs with the following query:

Select O.N2, O.N1
From
  tbc.dbf As O
Where
  O.N2 <> ''
And
  Exists (
Select I.N2, Count(I.N2)
From
  tbc.dbf As I
Where
  I.N2 = O.N2
Group By I.N2
Having Count(I.N2) <> 1)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question