...or create a sorted view and use COUNT=LAST or COUNT=1.
Regards, Stephen Markson The Pharmacy Examining Board of Canada 416.979.2431 x251 From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath Sent: December-28-14 07:46 To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Selecting a row using count and order by DESC INSERT INTO temptable (collist) SELECT TOP n collist FROM........ SELECT collist FROM temptable WHERE COUNT=LAST From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Michael J. Sinclair Sent: Saturday, December 27, 2014 12:48 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Selecting a row using count and order by DESC 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]<mailto:[email protected]>> To: RBASE-L Mailing List <[email protected]<mailto:[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]> [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]<mailto:[email protected]>> To: RBASE-L Mailing List <[email protected]<mailto:[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]<mailto:[email protected]>> wrote: Hi Mike, I work a lot with statistics, what I do basically is, * put results ORDER BY into a temp table and * add an autonum row then you can work/select the data however you want Fritz Von: [email protected]<mailto:[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
