Solved

SQL problem

Posted on 2004-10-19
21
145 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
Comment Utility
Have you tried something like this:

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

Expert Comment

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

Expert Comment

by:jonas78
Comment Utility
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
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
Depending on your database engine:

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

Author Comment

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

Author Comment

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

Expert Comment

by:Helena Marková
Comment Utility
What database is it ?
0
 
LVL 17

Expert Comment

by:geobul
Comment Utility
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
Comment Utility
>>> 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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 45

Expert Comment

by:aikimark
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

743 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now