Dan,
Close, but there appears to be some differences under 4.1 which are
interesting to say the least. Using:
SELECT * FROM customertable
WHERE <some criteria>
ORDER BY customertableid DESC
LIMIT 50
I get the expected result, i.e. 50 ordered in reverse.
Using:
(SELECT * FROM customertable
WHERE <some criteria>
ORDER BY customertableid DESC
LIMIT 50)
ORDER BY customertableid ASC;
I get the full set (272 results) ordered in reverse.
Here is the query:
set @job_coop = 'B28013';
set @cycle = 1;
set @type = 2;
set @test = 1;
set @cur_date=date_sub(curdate(),interval 7 day);
(select ftp_transfers.queue.ident
as 'ID',
ftp_transfers.queue.job_coop
as 'JCoop',
ftp_transfers.queue.cycle
as 'Cyc',
lpad(ftp_transfers.queue.status,10,' ')
as 'Status',
case when (ftp_transfers.queue.type = 1) then 'Internal'
when (ftp_transfers.queue.type = 2) then 'PDF To Coop'
when (ftp_transfers.queue.type = 3) then 'iVUE Zip To Coop'
when (ftp_transfers.queue.type = 4) then 'iVUE Zip To
Mandan' end as 'Type',
if(ftp_transfers.queue.test = 0,'No','Yes')
as 'Test',
lpad(format(ftp_transfers.queue.file_size,0),11,' ')
as 'Size',
substring(date_format(ftp_transfers.queue.queue_time,'%Y-%m-%d
%T'),12,8) as 'q_time',
ftp_transfers.queue.file_time
as 'f_time',
substring(ftp_transfers.queue.transfer_start,12,8)
as 't_start',
substring(ftp_transfers.queue.transfer_end,12,8)
as 't_end'
from ftp_transfers.queue
where ftp_transfers.queue.job_coop = @job_coop
and ftp_transfers.queue.type = @type
# and ftp_transfers.queue.test = @test
# and ftp_transfers.queue.cycle = @cycle
# and (@cur_date <= ftp_transfers.queue.queue_time)
# and param5 not like '%ebi%'
order by ftp_transfers.queue.ident desc limit 50)
order by ftp_transfers.queue.ident asc
I'm not in an immediate position to upgrade to 5.x at this point in time
and this is a non-critical issue for me. Thanks for you help and advice.
If someone has another solution, please chime in.
Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO
- USA Central Time Zone
636-755-2652 fax 636-755-2503
[EMAIL PROTECTED]
www.nisc.coop
> -----Original Message-----
> From: Dan Buettner [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, June 28, 2006 15:54
> To: Chris White
> Cc: mysql@lists.mysql.com
> Subject: Re: LIMIT Question
>
> Dirk, you could try this:
>
> (SELECT * FROM customertable
> WHERE <some criteria>
> ORDER BY customertableid DESC
> LIMIT 50)
> ORDER BY customertableid ASC;
>
> Like one sometimes does with UNIONs, but without any UNIONs. Didn't
> know whether it would work, but it does (on 5.0.21 anyway).
>
> That will give you the 50 entries with the highest ID numbers, sorted
> lowest to highest.
>
> Dan
>
>
> On 6/28/06, Chris White <[EMAIL PROTECTED]> wrote:
> > On Wednesday 28 June 2006 01:39 pm, Dirk Bremer wrote:
> > > Dan,
> > >
> > > That might be close. The rows are inserted with an auto-increment
> > > primary key, but I have no ready way of knowing what the
> latest 50-IDs
> > > are. There are also various date columns, but I won't
> readily know the
> > > dates in this scenario.
> >
> > That's why ORDER BY id DESC is used, it basically flips
> your table reverse, so
> > that last inserted (in essence the highest ID) is first,
> all the way down to
> > the first inserted (the lowest ID). Then LIMIT 50 will
> give you 50 from
> > highest id to lowest ID, or the last 50.
> > --
> > Chris White
> > PHP Programmer/DBlonde
> > Interfuel
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
