Solved

SQL problem

Posted on 2004-10-19
21
149 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
  • 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

838 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