pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOrder by YYYY MM DD in reverse chrono order trouble
12+ messages / 6 participants
[nested] [flat]
* Order by YYYY MM DD in reverse chrono order trouble
@ 2004-04-21 10:30 ogjunk-pgjedan@yahoo.com
2004-04-21 11:36 ` Re: Order by YYYY MM DD in reverse chrono order trouble denis@coralindia.com
2004-04-21 14:52 ` Re: Order by YYYY MM DD in reverse chrono order trouble Edmund Bacon <ebacon@onesystem.com>
2004-04-24 11:16 ` Re: Order by YYYY MM DD in reverse chrono order trouble Greg Sabino Mullane <greg@turnstep.com>
0 siblings, 3 replies; 12+ messages in thread
From: ogjunk-pgjedan@yahoo.com @ 2004-04-21 10:30 UTC (permalink / raw)
To: pgsql-sql
Hello,
I am trying to select distinct dates and order them in the reverse
chronological order. Although the column type is TIMESTAMP, in this
case I want only YYYY, MM, and DD back.
I am using the following query, but it's not returning dates back in
the reverse chronological order:
SELECT DISTINCT
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date)
FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
ui.id=uu.user_id
WHERE uus.x_id=1
ORDER BY
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date) DESC;
This is what the above query returns:
date_part | date_part | date_part
-----------+-----------+-----------
2004 | 2 | 6
2004 | 4 | 20
(2 rows)
I am trying to get back something like this:
2004 4 20
2004 4 19
2004 2 6
...
My query is obviously wrong, but I can't see the mistake. I was
wondering if anyone else can see it. Just changing DESC to ASC, did
not work.
Thank you!
Otis
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Order by YYYY MM DD in reverse chrono order trouble
2004-04-21 10:30 Order by YYYY MM DD in reverse chrono order trouble ogjunk-pgjedan@yahoo.com
@ 2004-04-21 11:36 ` denis@coralindia.com
2 siblings, 0 replies; 12+ messages in thread
From: denis@coralindia.com @ 2004-04-21 11:36 UTC (permalink / raw)
To: ogjunk-pgjedan@yahoo.com; pgsql-sql
Hi,
If you want to SORT descending considering multiple column, you need to
spefify DESC after each column.
Default is ASC.
So, your present sorting is ASC, ASC and DESC
You can specify
1 DESC, 2 DESC, 3 DESC
HTH
Denis
----- Original Message -----
From: <ogjunk-pgjedan@yahoo.com>
To: <pgsql-sql@postgresql.org>
Sent: Wednesday, April 21, 2004 4:00 PM
Subject: [SQL] Order by YYYY MM DD in reverse chrono order trouble
> Hello,
>
> I am trying to select distinct dates and order them in the reverse
> chronological order. Although the column type is TIMESTAMP, in this
> case I want only YYYY, MM, and DD back.
>
> I am using the following query, but it's not returning dates back in
> the reverse chronological order:
>
> SELECT DISTINCT
> date_part('year', uu.add_date), date_part('month', uu.add_date),
> date_part('day', uu.add_date)
>
> FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
> ui.id=uu.user_id
> WHERE uus.x_id=1
>
> ORDER BY
> date_part('year', uu.add_date), date_part('month', uu.add_date),
> date_part('day', uu.add_date) DESC;
>
>
> This is what the above query returns:
>
> date_part | date_part | date_part
> -----------+-----------+-----------
> 2004 | 2 | 6
> 2004 | 4 | 20
> (2 rows)
>
>
> I am trying to get back something like this:
> 2004 4 20
> 2004 4 19
> 2004 2 6
> ...
>
> My query is obviously wrong, but I can't see the mistake. I was
> wondering if anyone else can see it. Just changing DESC to ASC, did
> not work.
>
> Thank you!
> Otis
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Order by YYYY MM DD in reverse chrono order trouble
2004-04-21 10:30 Order by YYYY MM DD in reverse chrono order trouble ogjunk-pgjedan@yahoo.com
@ 2004-04-21 14:52 ` Edmund Bacon <ebacon@onesystem.com>
2004-04-22 09:56 ` Re: Order by YYYY MM DD in reverse chrono order trouble ogjunk-pgjedan@yahoo.com
2 siblings, 1 reply; 12+ messages in thread
From: Edmund Bacon @ 2004-04-21 14:52 UTC (permalink / raw)
To: pgsql-sql; +Cc: ogjunk-pgjedan@yahoo.com
Is there some reason you can't do this:
SELECT DISTINCT
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date)
FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
ui.id=uu.user_id
WHERE uus.x_id=1
ORDER BY
uu.add_date DESC;
This might be faster, as you only have to sort on one field, and I think it should give the desired results
ogjunk-pgjedan@yahoo.com wrote:
>Hello,
>
>I am trying to select distinct dates and order them in the reverse
>chronological order. Although the column type is TIMESTAMP, in this
>case I want only YYYY, MM, and DD back.
>
>I am using the following query, but it's not returning dates back in
>the reverse chronological order:
>
>SELECT DISTINCT
> date_part('year', uu.add_date), date_part('month', uu.add_date),
> date_part('day', uu.add_date)
>
>FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
>ui.id=uu.user_id
>WHERE uus.x_id=1
>
>ORDER BY
>date_part('year', uu.add_date), date_part('month', uu.add_date),
>date_part('day', uu.add_date) DESC;
>
>
>This is what the above query returns:
>
> date_part | date_part | date_part
>-----------+-----------+-----------
> 2004 | 2 | 6
> 2004 | 4 | 20
>(2 rows)
>
>
>I am trying to get back something like this:
>2004 4 20
>2004 4 19
>2004 2 6
>...
>
>My query is obviously wrong, but I can't see the mistake. I was
>wondering if anyone else can see it. Just changing DESC to ASC, did
>not work.
>
>Thank you!
>Otis
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
--
Edmund Bacon <ebacon@onesystem.com>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Order by YYYY MM DD in reverse chrono order trouble
2004-04-21 10:30 Order by YYYY MM DD in reverse chrono order trouble ogjunk-pgjedan@yahoo.com
2004-04-21 14:52 ` Re: Order by YYYY MM DD in reverse chrono order trouble Edmund Bacon <ebacon@onesystem.com>
@ 2004-04-22 09:56 ` ogjunk-pgjedan@yahoo.com
2004-04-22 13:01 ` Re: Order by YYYY MM DD in reverse chrono order trouble Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: ogjunk-pgjedan@yahoo.com @ 2004-04-22 09:56 UTC (permalink / raw)
To: pgsql-sql
Hello,
I'd love to be able to do that, but I cannot just ORDER BY uu.add_date,
because I do not have uu.add_date in the SELECT part of the statement.
The reason I don't have it there is because I need distinct YYYY MM DD
values back.
Is there a trick that I could use to make this more elegant?
Thanks,
Otis
--- Edmund Bacon <ebacon@onesystem.com> wrote:
> Is there some reason you can't do this:
>
> SELECT DISTINCT
> date_part('year', uu.add_date), date_part('month', uu.add_date),
> date_part('day', uu.add_date)
>
> FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
> ui.id=uu.user_id
> WHERE uus.x_id=1
>
> ORDER BY
> uu.add_date DESC;
>
> This might be faster, as you only have to sort on one field, and I
> think it should give the desired results
>
> ogjunk-pgjedan@yahoo.com wrote:
>
> >Hello,
> >
> >I am trying to select distinct dates and order them in the reverse
> >chronological order. Although the column type is TIMESTAMP, in this
> >case I want only YYYY, MM, and DD back.
> >
> >I am using the following query, but it's not returning dates back in
> >the reverse chronological order:
> >
> >SELECT DISTINCT
> > date_part('year', uu.add_date), date_part('month', uu.add_date),
> > date_part('day', uu.add_date)
> >
> >FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
> >ui.id=uu.user_id
> >WHERE uus.x_id=1
> >
> >ORDER BY
> >date_part('year', uu.add_date), date_part('month', uu.add_date),
> >date_part('day', uu.add_date) DESC;
> >
> >
> >This is what the above query returns:
> >
> > date_part | date_part | date_part
> >-----------+-----------+-----------
> > 2004 | 2 | 6
> > 2004 | 4 | 20
> >(2 rows)
> >
> >
> >I am trying to get back something like this:
> >2004 4 20
> >2004 4 19
> >2004 2 6
> >...
> >
> >My query is obviously wrong, but I can't see the mistake. I was
> >wondering if anyone else can see it. Just changing DESC to ASC, did
> >not work.
> >
> >Thank you!
> >Otis
> >
> >
> >---------------------------(end of
> broadcast)---------------------------
> >TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
> >
> >
>
> --
> Edmund Bacon <ebacon@onesystem.com>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Order by YYYY MM DD in reverse chrono order trouble
2004-04-21 10:30 Order by YYYY MM DD in reverse chrono order trouble ogjunk-pgjedan@yahoo.com
2004-04-21 14:52 ` Re: Order by YYYY MM DD in reverse chrono order trouble Edmund Bacon <ebacon@onesystem.com>
2004-04-22 09:56 ` Re: Order by YYYY MM DD in reverse chrono order trouble ogjunk-pgjedan@yahoo.com
@ 2004-04-22 13:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-22 13:37 ` Re: Order by YYYY MM DD in reverse chrono order trouble ogjunk-pgjedan@yahoo.com
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2004-04-22 13:01 UTC (permalink / raw)
To: ogjunk-pgjedan@yahoo.com; +Cc: pgsql-sql
<ogjunk-pgjedan@yahoo.com> writes:
> I'd love to be able to do that, but I cannot just ORDER BY uu.add_date,
> because I do not have uu.add_date in the SELECT part of the statement.
Sure you can. Back around SQL89 there was a restriction that ORDER BY
values had to appear in the SELECT list as well, but no modern database
has such a restriction anymore ...
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Order by YYYY MM DD in reverse chrono order trouble
2004-04-21 10:30 Order by YYYY MM DD in reverse chrono order trouble ogjunk-pgjedan@yahoo.com
2004-04-21 14:52 ` Re: Order by YYYY MM DD in reverse chrono order trouble Edmund Bacon <ebacon@onesystem.com>
2004-04-22 09:56 ` Re: Order by YYYY MM DD in reverse chrono order trouble ogjunk-pgjedan@yahoo.com
2004-04-22 13:01 ` Re: Order by YYYY MM DD in reverse chrono order trouble Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-04-22 13:37 ` ogjunk-pgjedan@yahoo.com
0 siblings, 0 replies; 12+ messages in thread
From: ogjunk-pgjedan@yahoo.com @ 2004-04-22 13:37 UTC (permalink / raw)
To: pgsql-sql
Hello,
Hm, doesn't work for me:
me@[local] mydb=> select distinct date_part('year', uu.add_date),
date_part('month', uu.add_date), date_part('day',
uu.add_date) from uus inner join ui on uus.user_id=ui.id inner join
uu on ui.id=uu.user_id where uus.subscriber_user_id=1 order by
uu.add_date desc;
ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target
list
I have this version of PostgreSQL installed: postgresql-7.3.4-3.rhl9
Thanks,
Otis
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> <ogjunk-pgjedan@yahoo.com> writes:
> > I'd love to be able to do that, but I cannot just ORDER BY
> uu.add_date,
> > because I do not have uu.add_date in the SELECT part of the
> statement.
>
> Sure you can. Back around SQL89 there was a restriction that ORDER
> BY
> values had to appear in the SELECT list as well, but no modern
> database
> has such a restriction anymore ...
>
> regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Order by YYYY MM DD in reverse chrono order trouble
2004-04-21 10:30 Order by YYYY MM DD in reverse chrono order trouble ogjunk-pgjedan@yahoo.com
@ 2004-04-24 11:16 ` Greg Sabino Mullane <greg@turnstep.com>
2 siblings, 0 replies; 12+ messages in thread
From: Greg Sabino Mullane @ 2004-04-24 11:16 UTC (permalink / raw)
To: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> I am trying to select distinct dates and order them in the reverse
> chronological order. Although the column type is TIMESTAMP, in this
> case I want only YYYY, MM, and DD back.
If you don't need them separated (which I suspect may be the case),
you can do something like this:
SELECT DISTINCT TO_CHAR(add_date,'YYYY-MM-DD') AS bb FROM tt
ORDER BY bb DESC;
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200404240716
-----BEGIN PGP SIGNATURE-----
iD8DBQFAikzOvJuQZxSWSsgRAgqbAKDC75SQd2aExYaniSJIzovOlVjvCACgyOAl
Q2KMp3YGBkQwy5y4h9r/96A=
=4idZ
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Order by YYYY MM DD in reverse chrono order trouble
@ 2004-04-21 10:58 Stijn Vanroye <s.vanroye@farcourier.com>
2004-04-21 12:20 ` Re: Order by YYYY MM DD in reverse chrono order trouble ogjunk-pgjedan@yahoo.com
0 siblings, 1 reply; 12+ messages in thread
From: Stijn Vanroye @ 2004-04-21 10:58 UTC (permalink / raw)
To: ogjunk-pgjedan@yahoo.com; pgsql-sql
> Hello,
>
> I am trying to select distinct dates and order them in the reverse
> chronological order. Although the column type is TIMESTAMP, in this
> case I want only YYYY, MM, and DD back.
>
> I am using the following query, but it's not returning dates back in
> the reverse chronological order:
>
> SELECT DISTINCT
> date_part('year', uu.add_date), date_part('month', uu.add_date),
> date_part('day', uu.add_date)
>
> FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
> ui.id=uu.user_id
> WHERE uus.x_id=1
>
> ORDER BY
> date_part('year', uu.add_date), date_part('month', uu.add_date),
> date_part('day', uu.add_date) DESC;
>
>
> This is what the above query returns:
>
> date_part | date_part | date_part
> -----------+-----------+-----------
> 2004 | 2 | 6
> 2004 | 4 | 20
> (2 rows)
>
>
> I am trying to get back something like this:
> 2004 4 20
> 2004 4 19
> 2004 2 6
> ...
>
> My query is obviously wrong, but I can't see the mistake. I was
> wondering if anyone else can see it. Just changing DESC to ASC, did
> not work.
>
> Thank you!
> Otis
What you could try to do in your order by clause is the following:
ORDER BY
date_part('year', uu.add_date) DESC,
date_part('month', uu.add_date) DESC,
date_part('day', uu.add_date) DESC;
That way you are sure each of the fields is sorted DESC. if you don't specify a direction in your order by clause postgres will take ASC as the default. I think that he does "ASC,ASC,DESC" instead. I'm not sure if he applies the DESC to all specified fields in the order by if you declare it only once.
Regards,
Stijn Vanroye
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Order by YYYY MM DD in reverse chrono order trouble
2004-04-21 10:58 Re: Order by YYYY MM DD in reverse chrono order trouble Stijn Vanroye <s.vanroye@farcourier.com>
@ 2004-04-21 12:20 ` ogjunk-pgjedan@yahoo.com
0 siblings, 0 replies; 12+ messages in thread
From: ogjunk-pgjedan@yahoo.com @ 2004-04-21 12:20 UTC (permalink / raw)
To: pgsql-sql
Thank you and Denis (denis@coralindia.com) - that was it. I needed
explicit DESC for each ORDER BY criterium.
Otis
--- Stijn Vanroye <s.vanroye@farcourier.com> wrote:
> > Hello,
> >
> > I am trying to select distinct dates and order them in the reverse
> > chronological order. Although the column type is TIMESTAMP, in
> this
> > case I want only YYYY, MM, and DD back.
> >
> > I am using the following query, but it's not returning dates back
> in
> > the reverse chronological order:
> >
> > SELECT DISTINCT
> > date_part('year', uu.add_date), date_part('month', uu.add_date),
>
> > date_part('day', uu.add_date)
> >
> > FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
> > ui.id=uu.user_id
> > WHERE uus.x_id=1
> >
> > ORDER BY
> > date_part('year', uu.add_date), date_part('month', uu.add_date),
> > date_part('day', uu.add_date) DESC;
> >
> >
> > This is what the above query returns:
> >
> > date_part | date_part | date_part
> > -----------+-----------+-----------
> > 2004 | 2 | 6
> > 2004 | 4 | 20
> > (2 rows)
> >
> >
> > I am trying to get back something like this:
> > 2004 4 20
> > 2004 4 19
> > 2004 2 6
> > ...
> >
> > My query is obviously wrong, but I can't see the mistake. I was
> > wondering if anyone else can see it. Just changing DESC to ASC,
> did
> > not work.
> >
> > Thank you!
> > Otis
> What you could try to do in your order by clause is the following:
> ORDER BY
> date_part('year', uu.add_date) DESC,
> date_part('month', uu.add_date) DESC,
> date_part('day', uu.add_date) DESC;
> That way you are sure each of the fields is sorted DESC. if you don't
> specify a direction in your order by clause postgres will take ASC as
> the default. I think that he does "ASC,ASC,DESC" instead. I'm not
> sure if he applies the DESC to all specified fields in the order by
> if you declare it only once.
>
>
> Regards,
>
> Stijn Vanroye
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your
> joining column's datatypes do not match
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Order by YYYY MM DD in reverse chrono order trouble
@ 2004-04-22 10:47 Stijn Vanroye <s.vanroye@farcourier.com>
0 siblings, 0 replies; 12+ messages in thread
From: Stijn Vanroye @ 2004-04-22 10:47 UTC (permalink / raw)
To: ogjunk-pgjedan@yahoo.com; +Cc: pgsql-sql
Yes indeed, I seem to have misinterpreted that last one. My apologies.
The distinct solution I mentioned isn't going to solve it, you are absolutely right in your example.
To get back on track:
You don't have to use a field in the select part of you query to be able to use it in the order by clause. So using order by add_date should indeed work. Since it appears that your add_date is a timestamp field (including time) it will order first on the date part, and next on the time part.
E.g.:
2004-04-12 12:45:22
2004-04-12 09:55:25
2004-04-11 14:25:31
2004-04-11 11:11:25
Since you have a distinct only on the date parts of the timestamp this will make no difference in the end result, it will still be sorted correctly on the date.
but I'm wondering if a GROUP BY wouldn't also be a good solution? like this:
<---CODE---->
SELECT
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date)
FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
ui.id=uu.user_id
WHERE uus.x_id=1
GROUP BY
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date);
<---END CODE---->
That way you don't need the DISTINCT part. The disadvantage however is that all the fields in the select must eighter be a part of the GROUP BY clause, or be used in an aggregate function.
Anyways: just descide whatever solution fits best for your needs.
Regards,
Stijn.
>Otis wrote:
> Hello,
>
> But will this work even with my add_date column, which is a TIMESTAMP
> field? Values in this column contain hours, minutes,
> seconds, etc., so
> won't DISTINCT return multiple rows for add_dates that _are_ distinct,
> but are on the same day.
>
> For example:
>
> 2004 04 02 11:22:33.034
> 2004 04 02 22:33:44.055
>
> Thanks,
> Otis
>
>
> --- Stijn Vanroye <s.vanroye@farcourier.com> wrote:
> > > Hello,
> > >
> > > I'd love to be able to do that, but I cannot just ORDER BY
> > > uu.add_date,
> > > because I do not have uu.add_date in the SELECT part of the
> > > statement.
> > > The reason I don't have it there is because I need
> distinct YYYY MM
> > DD
> > > values back.
> > > Is there a trick that I could use to make this more elegant?
> > yes, you could use:
> > SELECT DISTINCT ON (field1, field2) field1, field3, FieldN
> from table
> >
> > Regards,
> >
> >
> > Stijn Vanroye
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Order by YYYY MM DD in reverse chrono order trouble
@ 2004-04-23 07:14 Stijn Vanroye <s.vanroye@farcourier.com>
2004-04-23 07:53 ` Re: Order by YYYY MM DD in reverse chrono order trouble ogjunk-pgjedan@yahoo.com
0 siblings, 1 reply; 12+ messages in thread
From: Stijn Vanroye @ 2004-04-23 07:14 UTC (permalink / raw)
To: pgsql-sql; +Cc: ogjunk-pgjedan@yahoo.com
Indeed, it seems that I get the same result for a similar query.
I'm running version 7.3.4 on a rh 9 server.
Also: is the function date_part a function you wrote yourself? I get an error stating that the function date_part("Unknown",date) is not recognized.
It maybe not a solution to the actual problem but you could try this:
save the date and the time in two seperate fields. I use a similar construction for convenience.
Regards,
Stijn.
> Hello,
>
> Hm, doesn't work for me:
>
> me@[local] mydb=> select distinct date_part('year', uu.add_date),
> date_part('month', uu.add_date), date_part('day',
> uu.add_date) from uus inner join ui on uus.user_id=ui.id
> inner join
> uu on ui.id=uu.user_id where uus.subscriber_user_id=1 order by
> uu.add_date desc;
>
> ERROR: For SELECT DISTINCT, ORDER BY expressions must appear
> in target
> list
>
> I have this version of PostgreSQL installed: postgresql-7.3.4-3.rhl9
>
> Thanks,
> Otis
>
>
> --- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > <ogjunk-pgjedan@yahoo.com> writes:
> > > I'd love to be able to do that, but I cannot just ORDER BY
> > uu.add_date,
> > > because I do not have uu.add_date in the SELECT part of the
> > statement.
> >
> > Sure you can. Back around SQL89 there was a restriction that ORDER
> > BY
> > values had to appear in the SELECT list as well, but no modern
> > database
> > has such a restriction anymore ...
> >
> > regards, tom lane
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Order by YYYY MM DD in reverse chrono order trouble
2004-04-23 07:14 Re: Order by YYYY MM DD in reverse chrono order trouble Stijn Vanroye <s.vanroye@farcourier.com>
@ 2004-04-23 07:53 ` ogjunk-pgjedan@yahoo.com
0 siblings, 0 replies; 12+ messages in thread
From: ogjunk-pgjedan@yahoo.com @ 2004-04-23 07:53 UTC (permalink / raw)
To: pgsql-sql
Hello,
Thank you for all your help, Stijn.
date_part is a standard PG function.
While not the most elegant, the DESC, DESC, DESC solution suggested the
other day works okay for me, so I think I'll use that for now.
Thanks again!
Otis
--- Stijn Vanroye <s.vanroye@farcourier.com> wrote:
> Indeed, it seems that I get the same result for a similar query.
> I'm running version 7.3.4 on a rh 9 server.
>
> Also: is the function date_part a function you wrote yourself? I get
> an error stating that the function date_part("Unknown",date) is not
> recognized.
>
> It maybe not a solution to the actual problem but you could try this:
> save the date and the time in two seperate fields. I use a similar
> construction for convenience.
>
> Regards,
>
> Stijn.
>
> > Hello,
> >
> > Hm, doesn't work for me:
> >
> > me@[local] mydb=> select distinct date_part('year', uu.add_date),
> > date_part('month', uu.add_date), date_part('day',
> > uu.add_date) from uus inner join ui on uus.user_id=ui.id
> > inner join
> > uu on ui.id=uu.user_id where uus.subscriber_user_id=1 order by
> > uu.add_date desc;
> >
> > ERROR: For SELECT DISTINCT, ORDER BY expressions must appear
> > in target
> > list
> >
> > I have this version of PostgreSQL installed:
> postgresql-7.3.4-3.rhl9
> >
> > Thanks,
> > Otis
> >
> >
> > --- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > <ogjunk-pgjedan@yahoo.com> writes:
> > > > I'd love to be able to do that, but I cannot just ORDER BY
> > > uu.add_date,
> > > > because I do not have uu.add_date in the SELECT part of the
> > > statement.
> > >
> > > Sure you can. Back around SQL89 there was a restriction that
> ORDER
> > > BY
> > > values had to appear in the SELECT list as well, but no modern
> > > database
> > > has such a restriction anymore ...
> > >
> > > regards, tom lane
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faqs/FAQ.html
> >
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2004-04-24 11:16 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-21 10:30 Order by YYYY MM DD in reverse chrono order trouble ogjunk-pgjedan@yahoo.com
2004-04-21 11:36 ` denis@coralindia.com
2004-04-21 14:52 ` Edmund Bacon <ebacon@onesystem.com>
2004-04-22 09:56 ` ogjunk-pgjedan@yahoo.com
2004-04-22 13:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-22 13:37 ` ogjunk-pgjedan@yahoo.com
2004-04-24 11:16 ` Greg Sabino Mullane <greg@turnstep.com>
2004-04-21 10:58 Re: Order by YYYY MM DD in reverse chrono order trouble Stijn Vanroye <s.vanroye@farcourier.com>
2004-04-21 12:20 ` ogjunk-pgjedan@yahoo.com
2004-04-22 10:47 Re: Order by YYYY MM DD in reverse chrono order trouble Stijn Vanroye <s.vanroye@farcourier.com>
2004-04-23 07:14 Re: Order by YYYY MM DD in reverse chrono order trouble Stijn Vanroye <s.vanroye@farcourier.com>
2004-04-23 07:53 ` ogjunk-pgjedan@yahoo.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox