Sorry for the confusion. I will later find a better way to present the
issue.
But thanks for everyone's help.
On Sun, Aug 23, 2015 at 6:20 PM, Melvin Davidson <melvin6...@gmail.com>
wrote:
> Yes, I agree. I was a bit confused by the term "time" column. Not mention,
> the O/S and PG version were not given.
> If column t is truly type time, then only 24 rows can be returned
> regardless of limit, as in this Universe, there are only 24 hrs in time.
> However, if t is a timestamp, that is a whole other animal and the
> DISTINCT would have to be adjusted to include date & hour.
> Perhaps if we were given a more accurate table structure, a more exact
> solution could be provided.
>
> On Sun, Aug 23, 2015 at 6:09 PM, John McKown <john.archie.mck...@gmail.com
> > wrote:
>
>> 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.archie.mck...@gmail.com> wrote:
>>
>>> On Sun, Aug 23, 2015 at 4:05 PM, Tom Smith <tomsmith198...@gmail.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
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.
>
