position of record in sorted output

[fatkatie]

Table code, data, and basic sort is included below question.  (not homework)

I have a two tables which support log entries, a head table (hh) and a tail table (tt).
The table hh is the introduction table and contains unique information.  The table tt is related
to the head and contains text updates.  It looks exactly like a forum thread to me.

I can order the head table based on on dates found in the head and the tail. 

This is done so that records with the latest information appear first in a list.   What I want to do is
get the position, in that ordered table, of a head record given it's id.  I don't need the sorted table at all

just the position of a table hh.id in it.

   select h.id, h.created, t1.mx, t1.cnt,
          if (t1.mx is null, h.created, t1.mx) as usethisdate
     from hh as h
                     left join (select t.hid, max(t.created) as mx, count(t.hid) as cnt
                                  from tt as t
                              group by t.hid
                                ) as t1 ON h.id = t1.hid
 order by usethisdate desc;

1001    8/11/2017 12:58:29 PM    8/18/2017 12:58:29 PM    3    8/18/2017 12:58:29 PM
1003    8/13/2017 12:58:29 PM    8/15/2017 12:58:29 PM    1    8/15/2017 12:58:29 PM
1005    8/15/2017 12:58:29 PM    8/15/2017 12:58:29 PM
1002    8/12/2017 12:58:29 PM    8/14/2017 12:58:29 PM    2    8/14/2017 12:58:29 PM
1004    8/14/2017 12:58:29 PM    8/14/2017 12:58:29 PM
 

For example, say II want to know where, in this sorted output above, the position of head record 1005.  I would want the
result to tell me that it is the third record in the sorted table.

 

I tried to attach the whole table as a derived table, but I lost the order.  I think some variable use is in order here but I have never used them before and it's not working out.

 

Here's all the setup data is you wish to give it a shot:

SET FOREIGN_KEY_CHECKS = 0;
drop table if exists hh ;
drop table if exists tt ;
SET FOREIGN_KEY_CHECKS = 1;
    
create table hh (
   id       int   unique,  
   created  datetime,
   txt      varchar(20)
   );
   
   
create table tt (
   id       int,   
   hid      int,          
   created  datetime,
   txt      varchar(20),
   foreign key (hid)    references hh (id)
   );

insert into hh
   (id, created, txt)
values
   (1001, '2017-08-11 12:58:29', 'oldest h 11 t16-17-18');
insert into hh
   (id, created, txt)
values
   (1002, '2017-08-12 12:58:29', 'mid h 12 tail 13-14');
insert into hh
   (id, created, txt)
values
   (1003, '2017-08-13 12:58:29', 'mid h 13 tail 15');
insert into hh
   (id, created, txt)
values
   (1004, '2017-08-14 12:58:29', 'mid h 14');
insert into hh
   (id, created, txt)
values
   (1005, '2017-08-15 12:58:29', 'lastest h 15');
   

insert into tt
   (id, hid, created, txt)
values
   (2001, 1002, '2017-08-13 12:58:29', 't13 for h12');   
insert into tt
   (id, hid, created, txt)
values
   (2002, 1002, '2017-08-14 12:58:29', 't14 for h12');   
insert into tt
   (id, hid, created, txt)
values
   (2003, 1003, '2017-08-15 12:58:29', 't15 for h13');   
insert into tt
   (id, hid, created, txt)
values
   (2004, 1001, '2017-08-16 12:58:29', 't16 for h11');  
insert into tt
   (id, hid, created, txt)
values
   (2004, 1001, '2017-08-17 12:58:29', 't17 for h11');  
insert into tt
   (id, hid, created, txt)
values
   (2004, 1001, '2017-08-18 12:58:29', 't18 for h11');   
  

[fatkatie]

forgot....

Ver 15.1 Distrib 10.1.16-MariaDB, for Win32

[Psycho]

OK, you provided a LOT of details, but to be honest a lot of it was somewhat confusing. I *think* that what you want is simply to include a "rank" value in the results of that first query - is that correct?

 

Side note: You're going to alias table names that are 'hh' and 'tt' to 'h' and 't'? Seriously? If you are going to do that, at least give the tables meaningful names.

Edited August 16, 2017 by Psycho

[Jacques1]

MariaDB has window functions like ROW_NUMBER().

[Psycho]

FYI: There is no reason to use a subquery for what you already have. This would give the same results

 

   SELECT hh.id, hh.created, MAX(tt.created) AS mx, count(tt.hid) AS cnt,
           IF (count(tt.hid), MAX(tt.created), hh.created) AS usethisdate
    FROM hh
    LEFT JOIN tt ON tt.hid = hh.id
    GROUP BY hh.id
    ORDER BY usethisdate desc

 

Using the function Jacques1 shows above is definitely more flexible. This works on MySQL and may work for MariaDB too.

 

 

SET @row = 0;
SELECT data.*, @row := @row + 1 as row
FROM (
    SELECT hh.id, hh.created, MAX(tt.created) AS mx, count(tt.hid) AS cnt,
           IF (count(tt.hid), MAX(tt.created), hh.created) AS usethisdate
    FROM hh
    LEFT JOIN tt ON tt.hid = hh.id
    GROUP BY hh.id
    ORDER BY usethisdate desc
) AS data

[fatkatie]

Thanks all. 

 

Went with Murach's MySQL, 2nd Edition ($45!... which really isn't anything these days is it)
and

SELECT data.*, @row := @row + 1 as row
  FROM (
    SELECT hh.id, hh.created, MAX(tt.created) AS mx, count(tt.hid) AS cnt,
           IF (count(tt.hid), MAX(tt.created), hh.created) AS usethisdate
    FROM hh join (select @row := 0) x
    LEFT JOIN tt ON tt.hid = hh.id
    GROUP BY hh.id
    ORDER BY usethisdate desc
) AS data

Thanks both again.

Edited August 16, 2017 by fatkatie

[Jacques1]

This is a hack specifically for MySQL to work around the lack of window functions. Since MariaDB and almost all other database systems don't have this problem, the hack makes no sense here.

 

The only reasons why you would want this is to be able to downgrade to MySQL. I strongly recommend against that. MySQL is so far behind now that you should generally avoid it.

[fatkatie]

Thanks for the info.  I monkeyed it from another site thinking it was more secure, no pieces about.  As you can see I need to do some reading.  My sql skills are low hour pilot.  Thanks again.  Out.