Hi Dennis, Your idea works if I don't use the "into vdata" and gives me a nice little table that is ordered properly (asc or desc). But is there a way to capture the value from the last row in that table? Mike
________________________________ From: Dennis McGrath <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Saturday, December 27, 2014 12:22 PM Subject: [RBASE-L] - Re: Selecting a row using count and order by DESC The SELECT TOP syntax evaluates the where and order by clauses first and then gives you the desired x number of rows from the returned dataset. It was a very welcome addition to the syntax. try SELECT TOP 1 ColA into vdata from table order by ColB DESC Dennis McGrath From:[email protected] [mailto:[email protected]] On Behalf Of Karen Tellef Sent: Saturday, December 27, 2014 11:00 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Selecting a row using count and order by DESC I responded to this earlier, but as always happens I never see my posts so I don't know whether they go through. But IMO no it is not worth requesting. Your "where" clause is always going to be interpreted before the "order by" clause. To change that is to change a basic tenant of database theory and that should never be done lightly. So the "where count = 1" is going to find the first row, then do the "order by" later (which of course is meaningless for count = 1, but if you did a count = 10 it would get the first 10 rows then order them). If it's something you will do often, just define a permanent view that has the "order by" and do the count on the view. Karen -----Original Message----- From: Mike <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Sat, Dec 27, 2014 10:07 am Subject: [RBASE-L] - Re: AW: [RBASE-L] - Selecting a row using count and order by DESC Do you think it would be a reasonable request for enhancement to the select command to allow ascending and descending to work the way I described? Based on the published syntax it looks like what I'm trying to do should work but I can't make it work On Dec 27, 2014, at 10:32 AM, "Dr. Fritz Luettgens" <[email protected]> wrote: Hi Mike, >I work a lot with statistics, what I do basically is, >n put results ORDER BY into a temp table and >n add an autonum row >then you can work/select the data however you want >Fritz > >Von:[email protected] [mailto:[email protected]] Im Auftrag von Michael J. >Sinclair >Gesendet: Freitag, 26. Dezember 2014 18:01 >An: RBASE-L Mailing List >Betreff: [RBASE-L] - Selecting a row using count and order by DESC > >Hi All, >I am trying to select values from a row based on count. It seems to work as >expected when the order is ascending, but nothing changes when I make the >order descending. > >ColA ColB >------------------- >abc 1 >def 2 >ghi 3 > >When I do this.... > >SELECT ColA into vdata i1 from table where count =1 order by ColB ASC >then I get the variable >vdata = abc > >When I do this.... > >SELECT ColA into vdata from table where count =1 order by ColB DESC *(changed >ASC to DESC) >then I still get >vdata = abc > >What I want is to get when the order is DESC >vdata = ghi > >What am I doing wrong? >Is what I want possible? >Mike >
