Postgres Pro
Downloads
Home   >   mailing lists

Thread: Order by YYYY MM DD in reverse chrono order trouble

Order by YYYY MM DD in reverse chrono order trouble

From
Date:
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

Re: Order by YYYY MM DD in reverse chrono order trouble

From
"Stijn Vanroye"
Date:
> 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
postgreswill take ASC as the default. I think that he does "ASC,ASC,DESC" instead. I'm not sure if he applies the DESC
toall specified fields in the order by if you declare it only once. 


Regards,

Stijn Vanroye

Re: Order by YYYY MM DD in reverse chrono order trouble

From
Date:
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

Re: Order by YYYY MM DD in reverse chrono order trouble

From
Edmund Bacon
Date:
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>

Re: Order by YYYY MM DD in reverse chrono order trouble

From
Date:
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>
>

Re: Order by YYYY MM DD in reverse chrono order trouble

From
"Stijn Vanroye"
Date:
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
orderby add_date should indeed work. Since it appears that your add_date is a timestamp field (including time) it will
orderfirst 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
willstill 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
bea 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
>
>

Re: Order by YYYY MM DD in reverse chrono order trouble

From
Tom Lane
Date:
<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

Re: Order by YYYY MM DD in reverse chrono order trouble

From
Date:
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

Design Problem...

From
"Ryan Riehle"
Date:
Our business has multiple cost/profit centers that I call business units,
these are in a table called buinessunits.  We also have a table that holds a
list of services that are offerred by a business.  Each business unit has
many services it offers; 1 businees unit => Many Services. We want to be
able to query the cost/revenue/profit generated by each business unit and by
each service.  The problem is that it is possible that the service can be
switched to a different business unit, and then possibly back to the
original later on.  I've looked at multiple configurations, but have not
found a design that I feel is good so far.  Need to somehow track when a
particular service was associated with various businessunits.  If you want
more info, I can publish the tables for you. This same type of problem
exists in at least two other areas of this database we are developing.
Please help.

Kind Regards,
 -Ryan


-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of ogjunk-pgjedan@yahoo.com
Sent: Thursday, April 22, 2004 5:57 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Order by YYYY MM DD in reverse chrono order trouble


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>
>


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: Order by YYYY MM DD in reverse chrono order trouble

From
"Stijn Vanroye"
Date:
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
>

Re: Order by YYYY MM DD in reverse chrono order trouble

From
Date:
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
> >

Re: Order by YYYY MM DD in reverse chrono order trouble

From
"Greg Sabino Mullane"
Date:
-----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-----

Re: Design Problem...

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> The problem is that it is possible that the service can be switched to a
> different business unit, and then possibly back to the original later on.
First, you will get more responses if you do not create a new topic in
the middle of an existing thread. Here is a simplified answer to your problem.
Basically, you need to create a link between a business and a service,
and note when that link was created. You can grab the highest creation
time for a service to see which business currently owns it. Depending on
how often things change around, you may want to simply have a trigger on
the bs_map table that updates a "business" column in the services table,
rather than having to compute the max creation time constantly.
CREATE TABLE business ( id    SERIAL UNIQUE, bname VARCHAR
); 
CREATE TABLE service ( id    SERIAL UNIQUE, sname VARCHAR
); 
CREATE TABLE bs_map ( business INTEGER NOT NULL, service  INTEGER NOT NULL, assigned TIMESTAMPTZ NOT NULL DEFAULT
now()
);
ALTER TABLE bs_map ADD CONSTRAINT "bs_map_business_fk"FOREIGN KEY (business) REFERENCES business(id)ON DELETE RESTRICT
ONUPDATE CASCADE; 
 
ALTER TABLE bs_map ADD CONSTRAINT "bs_map_service_fk"FOREIGN KEY (service) REFERENCES service(id)ON DELETE RESTRICT ON
UPDATECASCADE; 
 
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200404241255
-----BEGIN PGP SIGNATURE-----
iD8DBQFAipwPvJuQZxSWSsgRApPVAJwPvc2aTadzTfKBJIge+2bh+IQ7qwCeN2rZ
MTilGUtbg0y4DOAENUzXc80=
=Jw5D
-----END PGP SIGNATURE-----

Re: Order by YYYY MM DD in reverse chrono order trouble

From
denis@coralindia.com
Date:
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)