i Know i should use 'AS', but ....

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

i Know i should use 'AS', but ....

Luuk
sqlite> .version
SQLite 3.26.0

sqlite> .headers on

sqlite> select 1 as X,date();
X|date()
1|2018-12-24
sqlite> select x,row_number() over (order by 1 desc) from (select 1 as x
union all select 2 union all select 3);
x|row_number() over (order by 1 desc)
3|1
2|2
1|3

Why are the headers missing in above query?



sqlite> select x,row_number() over (order by 1 desc) as Y from (select 1
as x union all select 2 union all select 3);
x|Y
3|1
2|2
1|3
sqlite>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: i Know i should use 'AS', but ....

Peter Johnson
The headers are present in all three queries you pasted.

The first result shows two rows, the top row is the header.

The other two results show 4 rows each, the top row of each is the header
row.

-P

On Tue, 25 Dec 2018, 3:42 AM Luuk <[hidden email] wrote:

> sqlite> .version
> SQLite 3.26.0
>
> sqlite> .headers on
>
> sqlite> select 1 as X,date();
> X|date()
> 1|2018-12-24
> sqlite> select x,row_number() over (order by 1 desc) from (select 1 as x
> union all select 2 union all select 3);
> x|row_number() over (order by 1 desc)
> 3|1
> 2|2
> 1|3
>
> Why are the headers missing in above query?
>
>
>
> sqlite> select x,row_number() over (order by 1 desc) as Y from (select 1
> as x union all select 2 union all select 3);
> x|Y
> 3|1
> 2|2
> 1|3
> sqlite>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

[OT] Re: i Know i should use 'AS', but ....

Luuk
On 24-12-2018 19:21, Peter Johnson wrote:

> The headers are present in all three queries you pasted.
>
> The first result shows two rows, the top row is the header.
>
> The other two results show 4 rows each, the top row of each is the header
> row.
>
> -P
>
> On Tue, 25 Dec 2018, 3:42 AM Luuk <[hidden email] wrote:
>
>> sqlite> .version
>> SQLite 3.26.0
>>
>> sqlite> .headers on
>>
>> sqlite> select 1 as X,date();
>> X|date()
>> 1|2018-12-24
>> sqlite> select x,row_number() over (order by 1 desc) from (select 1 as x
>> union all select 2 union all select 3);
>> x|row_number() over (order by 1 desc)
>> 3|1
>> 2|2
>> 1|3
>>
>> Why are the headers missing in above query?
>>
>
Why was i overlooking this?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [OT] Re: i Know i should use 'AS', but ....

Shawn Wagner
Using '.mode column" in conjunction with ".headers on" you're already using
makes it a lot more obvious.

On Mon, Dec 24, 2018, 12:20 PM Luuk <[hidden email] wrote:

> On 24-12-2018 19:21, Peter Johnson wrote:
> > The headers are present in all three queries you pasted.
> >
> > The first result shows two rows, the top row is the header.
> >
> > The other two results show 4 rows each, the top row of each is the header
> > row.
> >
> > -P
> >
> > On Tue, 25 Dec 2018, 3:42 AM Luuk <[hidden email] wrote:
> >
> >> sqlite> .version
> >> SQLite 3.26.0
> >>
> >> sqlite> .headers on
> >>
> >> sqlite> select 1 as X,date();
> >> X|date()
> >> 1|2018-12-24
> >> sqlite> select x,row_number() over (order by 1 desc) from (select 1 as x
> >> union all select 2 union all select 3);
> >> x|row_number() over (order by 1 desc)
> >> 3|1
> >> 2|2
> >> 1|3
> >>
> >> Why are the headers missing in above query?
> >>
> >
> Why was i overlooking this?
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [OT] Re: i Know i should use 'AS', but ....

Luuk

On 24-12-2018 21:25, Shawn Wagner wrote:
> Using '.mode column" in conjunction with ".headers on" you're already using
> makes it a lot more obvious.
>
>
like this:


SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select 1 as X,date() as d union all select 2,date() union all
select 3,datetime();
1|2018-12-25
2|2018-12-25
3|2018-12-25 18:56:41
sqlite> .mode column
sqlite> .headers on
sqlite> select 1 as X,date() as d union all select 2,date() union all
select 3,datetime();
X           d
----------  ----------
1           2018-12-25
2           2018-12-25
3           2018-12-25
sqlite> select 1 as X,datetime() as d union all select 2,date() union
all select 3,datetime();
X           d
----------  -------------------
1           2018-12-25 18:57:11
2           2018-12-25
3           2018-12-25 18:57:11
sqlite>



the first result seems correct (datetime after the 3)

in the secondresult the third line is casted to date? (or misses time?)

the above seems to be not correct when looking at the last query.

Same result in version 'SQLite version 3.26.0'




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: i Know i should use 'AS', but ....

Clemens Ladisch
Luuk wrote:
>sqlite> .mode column
>sqlite> .headers on
>sqlite> select 1 as X,date() as d union all select 2,date() union all
>select 3,datetime();
>X           d
>----------  ----------
>1           2018-12-25
>2           2018-12-25
>3           2018-12-25

The value is longer than the column with (which is probably based on the
values in the first line).

Try ".width 10 20".


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: i Know i should use 'AS', but ....

Luuk

On 26-12-2018 08:20, Clemens Ladisch wrote:

> Luuk wrote:
>> sqlite> .mode column
>> sqlite> .headers on
>> sqlite> select 1 as X,date() as d union all select 2,date() union all
>> select 3,datetime();
>> X           d
>> ----------  ----------
>> 1           2018-12-25
>> 2           2018-12-25
>> 3           2018-12-25
> The value is longer than the column with (which is probably based on the
> values in the first line).
>
> Try ".width 10 20".
>
A, now i know why i do not use 'mode column'....

It's too much typing work to find out how wide the columns are.

'.mode tabs' seems to fit my need more.

Thanks!


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users