Member Avatar for mbarandao

Hello:

I'm trying to get a record from mysql based on the query of another

the queries separately:

 set @row_num = 0; SELECT @row_num := @row_num + 1 as row_number,queue_num,clientID,customerid, todays_date, request, details,status FROM queue_sys
    ORDER BY todays_date;

outputs

row_number  queue_num   clientID    customerid  todays_date     request details status
1   14  44DWS   63097   2012-06-15 07:44:59 A/C-Heater Controls asfaf   drop-off
2   15  V8MONST SB3434  2012-06-15 07:45:40 Anti-Lock Systems ,Brake Drums  2323    waiting
3   16  342DF   Bs6444  2012-06-15 08:00:03 Oil Change  sadasd  drop-off

second query

SELECT * FROM queue_sys ORDER BY clientID DESC LIMIT 1;

How can run both queries in one sql to output:

3   16  342DF   Bs6444  2012-06-15 08:00:03 Oil Change  sadasd  drop-off

I have tried the following with no success.

 SELECT * FROM (set @row_num = 0; SELECT @row_num := @row_num + 1 as row_number,queue_num,clientID,customerid, todays_date, request, details,status FROM queue_sys
    ORDER BY todays_date)ORDER BY clientID DESC LIMIT 1;

Thanks!

Edited by mbarandao
  • 2 Contributors
  • 3 Replies
  • 193 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by mbarandao
Member Avatar for 1016

I dont know if I have understood what you need, my interpretation is:
I want the data of the row with the littler clientID but i want the place it has in a list ordered by todays_date.

If I'm right you could try this query:

SELECT (SELECT COUNT(*) FROM queue_sys b WHERE b.todays_date < a.todays_date) + 1 as row_number,
       queue_num, clientID, customerid, todays_date, request, details, status 
FROM   queue_sys a
WHERE  a.clientID = (SELECT MIN(c.clientID) FROM queue_sys c)

Good luck!

Member Avatar for mbarandao

Thank you 1016 for your reply;

Your understanding is indeed correct; however, your suggested code returns the record before the last. The absolute last entered record behond three is not pulled.

my actual table

queue_num clientID customerid todays_date updated request details status 
14 44DWS 63097 2012-06-15 07:44:59 2012-06-15 08:41:42 A/C-Heater Controls asfaf drop-off 
16 342DF Bs6444 2012-06-15 08:00:03 2012-06-15 09:49:21 Oil Change sadasd drop-off 
28 V8MONST SB3434 2012-06-15 12:58:21 0000-00-00 00:00:00 A/C System Repair dfsdfsdf waiting

The output from suggested code

row_number  queue_num   clientID    customerid  todays_date request details status
    2   16  342DF   Bs6444  2012-06-15 08:00:03 Oil Change  sadasd  drop-off

I need the output to be

row_number queue_num clientID customerid todays_date request details status 
3 28 V8MONST SB3434 2012-06-15 12:58:21 A/C System Repair dfsdfsdf waiting

Any thoughts!

Edited by mbarandao
Member Avatar for mbarandao

Solved!
modifying 1016's suggestion with:

 SELECT (SELECT COUNT(*) FROM queue_sys b WHERE b.todays_date < a.todays_date) + 1 as row_number,
           queue_num, clientID, customerid, todays_date, request, details, status 
    FROM   queue_sys a
    WHERE  a.queue_num = (SELECT MAX(c.queue_num) FROM queue_sys c)

Works as intended.

Thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.