Обсуждение: order by date desc but NULLs last
I have a simple query that sorts by descending date but the NULL dates show up first. Is there a way I can sort so they come last without sorting ascending? SELECT submittime,score,gradedtime FROM student_gradedmaterial WHERE gradedmaterialid=3 and studentid=102 order by gradedtime desc; submittime | score | gradedtime ---------------------+-------+---------------------------- 2003-01-30 22:56:38 | | 2003-01-31 03:42:29 | 99 | 2003-02-0614:21:43.043587 but what I want is all the graded items first in gradedtime desc and NULL afterwards. I do need to keep the NULL score rows. (So I get the latest submitted grade for the assignment but also any ungraded submission information.) Thanks for any advice.><><><><><><><><>< AgentM agentm@cmu.edu
On Sun, Feb 09, 2003 at 05:29:29PM -0500, A.M. wrote: > I have a simple query that sorts by descending date but the NULL dates > show up first. Is there a way I can sort so they come last without > sorting ascending? > > SELECT submittime,score,gradedtime FROM student_gradedmaterial WHERE > gradedmaterialid=3 and studentid=102 order by gradedtime desc; > > submittime | score | gradedtime > ---------------------+-------+---------------------------- > 2003-01-30 22:56:38 | | > 2003-01-31 03:42:29 | 99 | 2003-02-06 14:21:43.043587 > > but what I want is all the graded items first in gradedtime desc and > NULL afterwards. I do need to keep the NULL score rows. (So I get the > latest submitted grade for the assignment but also any ungraded > submission information.) You need to ORDER BY a _function_ of the gradedtime column, substituting an extreme value for NULL. Try this: SELECT submittime,score,gradedtime FROM student_gradedmaterialWHERE gradedmaterialid=3 and studentid=102 order bycoalesce(gradedtime,'-infinity')desc; Ross
> I have a simple query that sorts by descending date but the NULL > dates show up first. Is there a way I can sort so they come last > without sorting ascending? > > SELECT submittime,score,gradedtime FROM student_gradedmaterial WHERE > gradedmaterialid=3 and studentid=102 order by gradedtime desc; > > submittime | score | gradedtime > ---------------------+-------+---------------------------- > 2003-01-30 22:56:38 | | > 2003-01-31 03:42:29 | 99 | 2003-02-06 14:21:43.043587 > > but what I want is all the graded items first in gradedtime desc and > NULL afterwards. I do need to keep the NULL score rows. (So I get > the latest submitted grade for the assignment but also any ungraded > submission information.) > > Thanks for any advice. > ><><><><><><><><>< > AgentM > agentm@cmu.edu order by gradetime is null asc, gradetime desc; Regards, Tomasz Myrta
Why not try the obvious first? order by gradedtime is null, gradedtime desc; "Ross J. Reedstrom" wrote: > > On Sun, Feb 09, 2003 at 05:29:29PM -0500, A.M. wrote: > > I have a simple query that sorts by descending date but the NULL dates > > show up first. Is there a way I can sort so they come last without > > sorting ascending? > > > > SELECT submittime,score,gradedtime FROM student_gradedmaterial WHERE > > gradedmaterialid=3 and studentid=102 order by gradedtime desc; > > > > submittime | score | gradedtime > > ---------------------+-------+---------------------------- > > 2003-01-30 22:56:38 | | > > 2003-01-31 03:42:29 | 99 | 2003-02-06 14:21:43.043587 > > > > but what I want is all the graded items first in gradedtime desc and > > NULL afterwards. I do need to keep the NULL score rows. (So I get the > > latest submitted grade for the assignment but also any ungraded > > submission information.) > > You need to ORDER BY a _function_ of the gradedtime column, substituting > an extreme value for NULL. Try this: > > SELECT submittime,score,gradedtime FROM student_gradedmaterial > WHERE gradedmaterialid=3 and studentid=102 order by > coalesce(gradedtime,'-infinity') desc; > > Ross > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
On Mon, Feb 10, 2003 at 01:32:54PM -0500, Jean-Luc Lachance wrote: > Why not try the obvious first? Because it's not always obvious ;-) Yup, if you want two kinds of order (NULLs last, descending dates), use two order by clauses. > order by gradedtime is null, gradedtime desc; > > "Ross J. Reedstrom" wrote: <a different, less elegant way to get the NULLs last> Ross