How to do multiple join / group by selects using sqlite3?

Question

I have a sqlite3 database with one table called orig:

CREATE TABLE orig (sdate date, stime integer, orbnum integer);

What I want to do is select the first date/time for each orbnum. The only problem is that stime holds the time as a very awkward integer.

Assuming a six-digit number, the first two digits show the hour, the 3./4. show the minutes, and the last two digits show the seconds. So a value of 12345 is 1:23:45, whereas a value of 123456 is 12:34:56.

I figured I'd do this using two nested join/group statements, but somehow I cannot get it to work properly. Here's what I've got so far:

select s.orbnum, s.sdate, s.stime
from (
    select t.orbnum, t.sdate, t.stime, min(t.sdate) as minsdate
    from (
        select orbnum, sdate, stime, min(stime) as minstime
        from scia group by orbnum, sdate
    ) as t inner join orig as s on s.stime = t.minstime and s.sdate = t.sdate and s.orbnum = t.orbnum
) as d inner join scia as s on s.stime = d.stime and s.sdate = minsdate and s.orbnum = d.orbnum
where s.sdate >= '2002-08-01' limit 0,200;

This is the error I get:

Error: no such column: t.orbnum

I'm sure it's just some stupid mistake, but actually, I'm quite new to SQL ...

Any help is greatly appreciated :)

Edit:

After fixing the obvious typo, the query runs -- but returns an empty result set. However, the table holds ~10yrs of data, with about 12 orbnums per day and about 4-5 different times per orbnum. So I guess there's some mistake in the logic of the query ...

Answer 1

In your last join, you have d, which is the result of your double nested select, and you join s on it. From there, t is not visible. That’s why you get the “no such column: t.orbnum” error. Maybe you meant s.orbnum = d.orbnum?