Query ordering question

From: ajmcello <ajmcello78(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Query ordering question
Date: 2012-07-02 23:22:32
Message-ID: CANuED-M7uToVenjfoK55W-3V6hhFap9p06PHYvKrjFSTbw5-AQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm interested in sorting my query by time descending, with the
highest percent by latest time shown first, and then every other
record associated with column name sorted by time descending,
following the first record. Does that make sense?

The first query is the best I've come up with. The second, is how I'd
like it took.

Any suggestions?

Thanks in advance.

db=# SELECT name,date,percent,price,time,amount FROM name WHERE amount
>= '1000000' AND date='$today' ORDER BY percent DESC;

name | date | percent | price | time | amount
--------+------------+----------------+---------+----------+----------
BOB | 2012-07-02 | 63.77 | 8.86 | 14:59:00 | 26975372
BOB | 2012-07-02 | 63.77 | 8.86 | 15:01:00 | 27001372
BOB | 2012-07-02 | 64.06 | 8.8755 | 15:04:00 | 27145552
BOB | 2012-07-02 | 63.77 | 8.86 | 14:57:00 | 26946338
GIL | 2012-07-02 | 38.72 | 0.6789 | 14:55:00 | 1012880
GIL | 2012-07-02 | 38.74 | 0.679 | 14:59:00 | 1026190
GIL | 2012-07-02 | 38.74 | 0.679 | 14:57:00 | 1017480
GIL | 2012-07-02 | 38.95 | 0.68 | 15:01:00 | 1027590
BUB | 2012-07-02 | 22.8 | 4.1262 | 14:57:00 | 7300694
BUB | 2012-07-02 | 22.02 | 4.1 | 14:59:00 | 7346968
BUB | 2012-07-02 | 22.47 | 4.115 | 15:01:00 | 7371456
BUB | 2012-07-02 | 22.02 | 4.1 | 15:04:00 | 7406511
BUB | 2012-07-02 | 23.84 | 4.1611 | 14:55:00 | 7221402
SAL | 2012-07-02 | 21.5 | 41.335 | 15:02:00 | 71965920
SAL | 2012-07-02 | 21.5 | 41.335 | 15:05:00 | 71986544
SAL | 2012-07-02 | 21.5 | 41.335 | 15:00:00 | 71943280
SAL | 2012-07-02 | 21.52 | 41.34 | 14:55:00 | 71903096
SAL | 2012-07-02 | 21.5 | 41.335 | 14:58:00 | 71921496

name | date | percent | price | time | amount
--------+------------+----------------+---------+----------+----------
BOB | 2012-07-02 | 64.06 | 8.8755 | 15:04:00 | 27145552
BOB | 2012-07-02 | 63.77 | 8.86 | 15:01:00 | 27001372
BOB | 2012-07-02 | 63.77 | 8.86 | 14:59:00 | 26975372
BOB | 2012-07-02 | 63.77 | 8.86 | 14:57:00 | 26946338
BOB | 2012-07-02 | 63.96 | 8.87 | 14:55:00 | 26935038
GIL | 2012-07-02 | 38.95 | 0.68 | 15:01:00 | 1027590
GIL | 2012-07-02 | 38.74 | 0.679 | 14:59:00 | 1026190
GIL | 2012-07-02 | 38.74 | 0.679 | 14:57:00 | 1017480
GIL | 2012-07-02 | 38.72 | 0.6789 | 14:55:00 | 1012880
BUB | 2012-07-02 | 22.02 | 4.1 | 15:04:00 | 7406511
BUB | 2012-07-02 | 22.47 | 4.115 | 15:01:00 | 7371456
BUB | 2012-07-02 | 22.02 | 4.1 | 14:59:00 | 7346968
BUB | 2012-07-02 | 22.8 | 4.1262 | 14:57:00 | 7300694
BUB | 2012-07-02 | 23.84 | 4.1611 | 14:55:00 | 7221402
SAL | 2012-07-02 | 21.5 | 41.335 | 15:05:00 | 71986544
SAL | 2012-07-02 | 21.5 | 41.335 | 15:02:00 | 71965920
SAL | 2012-07-02 | 21.5 | 41.335 | 15:00:00 | 71943280
SAL | 2012-07-02 | 21.5 | 41.335 | 14:58:00 | 71921496
SAL | 2012-07-02 | 21.52 | 41.34 | 14:55:00 | 71903096

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2012-07-03 00:11:19 Re: Query ordering question
Previous Message Kevin Grittner 2012-07-02 16:14:58 Re: Postgresql 9.0.6 alway run VACUUM ANALYZE pg_catalog.pg_attribute