The MIN 2

[foxup]

Hi All,

I have a .DBF which has 4 fields. They are: "ID", "HGROUP", "LEVEL", "EMAIL". What I would like to get is the "email" by finding the lowest of the "LEVEL", then "ID" for a particular "HGROUP". Here is an extract of .DBF values:

ID HGROUP LEVEL EMAIL
237 44 2 abc123@123.com
238 44 1 mike@try.ca
239 44 1 joey@st.ca
241 44 3 helloworld@niceone.com

So, if I enter '44' as an input, I would need to get a result as "mike@try.ca"


Any help on getting this with a SELECT or something?


Thanks,
FOXUP!

 

[dbMark]

Are these numeric or character fields? Here's a simple sample for 5 digit numeric fields:

INDEX ON STR(HGROUP,5)+STR(LEVEL,5) TAG GroupLevel
SET ORDER TO GroupLevel
IF SEEK(STR(MyGroupVar,5))
   ? EMAIL
ELSE
   ? "Group not found"
ENDIF

In the example above it's okay if the index conversion to string size is larger than the actual field size, it just can't be smaller.

 

[Tore Bleken]

select top 1 * from yourtable where hgroup = 44 order by level

 

[Olaf Doschke]

select top 1 * from yourtable where hgroup = 44 order by level, id

the lowest of the "LEVEL", then "ID"

Bye, Olaf.

 

[foxup]

Wow, that was easy

Thanks to everybody. Works like a charm. Star for all.


FOXUP!

 

[foxup]

OK, actually 1 last tweek. How can I get a SELECT of the same criteria from the same DBF, BUT for every DIST HGROUP in that DBF (instead of asking user the HGROUP) ??

 

[Tore Bleken]

Just remove the Where clause and the condition that follows it.

 

[foxup]

Nah, if I just remove the "where" clause it doesn't work any help please?

 

[Tore Bleken]

I guess I misunderstood. Is this what you want?

select distinct hgroup from yourtable

 

[Olaf Doschke]

No, I think foxup wants the top1 per group, per HGROUP. That's not at all a simple change, as you can't use TOP n in a correlated sub-query, which would be needed.
The problem is age old, for example a lengthy discussion about that was in thread184-1280841. A solution not using SQL fits better in this case.

First a preparation

USE yourtable
INDEX ON BINTOC(HGROUP)+BINTOC(LEVEL)+BINTOC(ID) TAG GrpLvlId

This is separate from the following code, as ou only need to index once, it doesn't belong into the code you may repeat at any time to get current major EMAIL addresses.

Local lcHGROUP, llExact
Create Cursor curResult (HGROUP I, EMAIL C(128))
* Tore Bleken's query is fine here to find all hgroups.
* If you have a table, where hgroup is unique, you may also use that for the scan loop.
Select Distinct HGROUP From yourtable Into Cursor curHGROUPs && this also opens yourtable, if it's not yet opened.
llExact = (Set("Exact")="ON")
Set Exact Off
Scan && all hgroups
   * find first email of hgroup with lowest level and the lowest id within that lowest level
   Seek(BinToC(curHGROUPs.HGROUP),"yourtable","GrpLvlId")
   Insert Into curResult Values(yourtable.HGROUP, yourtable.EMAIL)
Endscan
If m.llExact
   Set Exact On
Endif

The cursor curResult now contains the EMAIL per HGROUP.

Bye, Olaf.

 

[brigmar]

Assuming id is unique...

Select hgroup,min(level) as level from yourtable group by 1 into cursor s1
Select yt.hgroup,min(yt.id) as id from yourtable yt inner join s1 on yt.hgroup=s1.hgroup and yt.level=s1.level group by 1 into cursor s2
Select * from yourtable where id in (select id from s2) into cursor s3

The cursor s3 now contains the lowest id for the lowest level for each hgroup.

 

[Olaf Doschke]

Create Cursor yourtable(ID I, HGROUP I, Level I, EMAIL C(128))
Index on id tag xid
Index on hgroup tag xgroup
Index on BinToC(hgroup)+BinToC(level)+BinToC(id) tag GrpLvlId

Text to lcData NOSHOW
237,44,2,'abc123@123.com'
238,44,1,'mike@try.ca'
239,44,1,'joey@st.ca'
241,44,3,'helloworld@niceone.com'
EndText
ALines(laRows,lcData)
For each lcRow in laRows
   Insert into yourtable Values(&lcRow)
EndFor

* create more sample data
Set notify cursor off
Select * from yourtable into cursor dummy readwrite
Select yourtable
For i = 1 to 10000
   Update dummy set hgroup=hgroup+1, id=id+5,EMAIL=Sys(2015)+"@nowhere.com"
   Append From Dbf("dummy")
EndFor

t0 = Seconds()
Local lcHGROUP, llExact
Create Cursor curResult (HGROUP I, EMAIL C(128))
* Tore Bleken's query is fine here to find all hgroups.
* If you have a table, where hgroup is unique, you may also use that for the scan loop.
Select Distinct HGROUP From yourtable Into Cursor curHGROUPs && this also opens yourtable, if it's not yet opened.
llExact = (Set("Exact")="ON")
Set Exact Off
Scan && all hgroups
   * find first email of hgroup with lowest level and the lowest id within that lowest level
   =Seek(BinToC(curHGROUPs.HGROUP),"yourtable","GrpLvlId")
   Insert Into curResult Values(yourtable.HGROUP, yourtable.EMAIL)
Endscan
If m.llExact
   Set Exact On
Endif 
t1 = Seconds()
? "xbase", t1-t0

t0 = Seconds()
Select hgroup,min(level) as level from yourtable group by 1 into cursor s1
Select yt.hgroup,min(yt.id) as id from yourtable yt inner join s1 on yt.hgroup=s1.hgroup and yt.level=s1.level group by 1 into cursor s2
Select * from yourtable where id in (select id from s2) into cursor s3 
t1 = Seconds()
? "sql", t1-t0

This shows the sql solution is slower, takes about double the time. Not a big factor, the sql code is shorter, easy to understand and maintain.

Note: The preparation of mass data is what takes longest.
Note 2: the sql profits from any caching done during the run of xbase loop, but still is slower.
Note 3: The seek function call here needs the "=" preceeding it, otherwise VFP assumes the SEEK Command with an expression in brackets instead of the SEEK function call.

Bye, Olaf.

 

[Olaf Doschke]

If you take the sql solution I'd not use where id in (subselect) in the last query, but another inner join, as in the second query:

[pre]Select yourtable.* from yourtable inner join s2 on yourtable.id=s2.id into cursor s3[/pre]

This inner join can make use of the index on yourtable.id you surely have (in my sample code it's xid), whereas "id in (select id from s2)" makes rushmore create a temp index on s2.id, as s2 is a query result without any index and "yourtable.id in (query)" is an operation looking up yourtable.id in the query result set and not each id of this set in the index on yourtable.id, as the inner join clause can do.

Bye, Olaf.

 

[Olaf Doschke]

One final thing:

You can also make sql use the index on the triple BINTOC expression:

Select Distinct HGROUP From yourtable Into Cursor curHGROUPs
Select yt.* from yourtable yt inner join curHGROUPs ;
on BinToC(yt.hgroup)+BinToC(yt.level)+BinToC(yt.id)=BinToC(curHGROUPs.HGROUP) ;
Into Cursor curResult

But this is still slower than the scan loop AND also slower than brigmar's 3 query solution, even though BinToC(yt.hgroup)+BinToC(yt.level)+BinToC(yt.id) is not computed but BinToC(curHGROUPs.HGROUP) is looked up in the index GrpLvlId.

Bye, Olaf.

 

[TamarGranor]

If you only need the top 1 for each group, this is reasonably easy. If you need more than 1, it's hard.

Untested, but The easy answer should be :

SELECT HGROUP, Email ;
  FROM YourTable YT3 ;
  JOIN ( ;
    SELECT HGROUP, MIN(ID) AS MinID ;
      FROM YourTable YT2;
        JOIN ( ;
          SELECT HGROUP, MIN(Level) AS MinLevel ;
             FROM YourTable YT1;
             GROUP BY HGROUP) csrMinLevel ;
          ON YT2.HGROUP = csrMinLevel.HGROUP ;
          AND YT2.Level = csrMinLevel.MinLevel ;
      GROUP BY HGROUP) csrMinID ;
    ON YT3.HGROUP = csrMinID.HGROUP ;
    AND YT3.ID = csrMinID.MinID ;
  INTO CURSOR csrResult

Tamar