Re: fastes way to retrieve segmented without using loop

Melvin's use of DISTINCT ON (...) is superior to my use of DISTINCT(...)
because it doesn't return the value to your program. I keep forgetting this
way. I learned it the other way. Old dog + new trick == problem.

On Sun, Aug 23, 2015 at 5:04 PM, John McKown <john(dot)archie(dot)mckown(at)gmail(dot)com>
wrote:

> On Sun, Aug 23, 2015 at 4:05 PM, Tom Smith <tomsmith1989sk(at)gmail(dot)com>
> wrote:
>
>> Hello:
>>
>> I have a time series table,
>> using below sql, loop (psque code), I can get one row for each hour
>>
>
> ​s/psque/pseudo/g;​
>
>
>
>>
>> for( H=1: H< 99999; H++){
>> select * from table where t >= H and t < H+1 limit 1
>> }
>>
>> t (time column) is indexed).
>>
>> Is there a better way to use a issue a SINGLE SQL
>> with an array of time start/end pair
>> so it will be executed once to send back result, which would be much
>> faster
>> than issuing sql again and again (even with prepared statement and using
>> sql function).
>>
>> Thanks in Advance
>>
>
> Well, I have a bit of a problem if "t" is a "time column". Do you mean a
> "time interval"? Or do you really mean it is an integer of some sort. I ask
> because H sure looks like a plain old integer to me.
>
> In any case, if "t" is an "int" as opposed to a "time interval", then you
> could start with something like:
>
> SELECT DISTINCT(t), ???? columns ???? FROM table WHERE t BETWEEN 1 AND
> 99999;
>
> But if "t" really is a "time interval" in the PostgreSQL sense, and H is
> like the "hour" portion (H --> Hour, makes sense to this weirdo). And you
> want to select one row of data where the "t" interval is 1 hour, another
> where the "t" interval is 2 hours, another where the "t" interval is 3
> hours, and so on up to an interval of at most 99_999 hours. Then you might
> need something like:
>
> SELECT DISTINCT(EXTRACT(HOUR FROM t)) AS interval_truncated_to_hour, *
> FROM table WHERE t BETWEEN 1 AND 99999;
>
> I don't know of a way to eliminate the first field from the result. But,
> in reality, I would _never_ issue a SELECT * in a "normal" program. Only
> ask for the columns you are actually going to need. Because, someday,
> someone, is going to change the schema on the table and your program is
> (im)pure porcine excrement at that point. With no errors returned to it.
> IMO, it is an unshakable rule to ONLY and ALWAYS specify the variable
> names. The only exception is if your program actually examines the schema
> of the table before doing a SELECT and dynamically constructs it.
>
>
>
>
>
>
> --
>
> Schrodinger's backup: The condition of any backup is unknown until a
> restore is attempted.
>
> Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
>
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
> Maranatha! <><
> John McKown
>

--

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown