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.
