CODEWITHSUNDEEP
mysqlsql
AJT
AJT

Reputation: 25

mySQL return specific rows per field

I'm going to try my best to explain this... I'm looking to get 5 random rows to return for each field. For example, I have:

SELECT "Name","Ticket ID","Channel"
FROM Tickets
  WHERE "Ticket Created at" between date('2017-09-01') AND date('2017-10-01')
  AND "Channel" = 'Phone' or 'Chat' or 'Email'
  AND "First Agent Primary Email" = '[email protected]'

And what I want to do is for the table to return 5 random ticket IDs per phone channel, per chat channel or per email channel.

Can anyone help?

Upvotes: 1

Views: 46

Answers (5)

nish
nish

Reputation: 1221

Try this query.

SELECT Name,Ticket ID,Channel FROM Tickets
WHERE Ticket Created at between date('2017-09-01') 
AND date('2017-10 
01')
AND Channel IN ( 'Phone', 'Chat' , 'Email')
AND First Agent Primary Email = '[email protected]'
ORDER BY  RAND() LIMIT 5;

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

The other answers all seemed to have problems with their date range, as did your original query, so I decided to post this:

SELECT
    Name, `Ticket ID`, Channel
FROM Tickets
WHERE 
    `Ticket Created at` >= '2017-09-01' AND `Ticket Created at` < '2017-10-01' AND
    Channel IN ('Phone', 'Chat', 'Email') AND
    `First Agent Primary Email` = '[email protected]'
ORDER BY
    RAND()
LIMIT 5;

The critical thing is that if you want restrict to the month of September, you need to phrase it carefully when using date literals. The following is a trap which is to be avoided:

WHERE `Ticket Created at` BETWEEN '2017-09-01' AND '2017-10-01'

This will possibly include data which happens to fall on midnight of October 1, probably not what you have in mind. And also, you don't need to wrap date literals with DATE; MySQL can already handle them if in the right format (you used the right format).

Edit:

If you want 5 random tickets for each of the three channels, you can do so with a union query:

SELECT Name, `Ticket ID`, Channel
FROM
(
    SELECT Name, `Ticket ID`, Channel
    FROM Tickets
    WHERE `Ticket Created at` >= '2017-09-01' AND
          `Ticket Created at` < '2017-10-01' AND
          Channel = 'Phone' AND
         `First Agent Primary Email` = '[email protected]'
    ORDER BY RAND() LIMIT 5
) t1
UNION ALL
(
    SELECT Name, `Ticket ID`, Channel
    FROM Tickets
    WHERE `Ticket Created at` >= '2017-09-01' AND
          `Ticket Created at` < '2017-10-01' AND
          Channel = 'Chat' AND
         `First Agent Primary Email` = '[email protected]'
    ORDER BY RAND() LIMIT 5
) t2
UNION ALL
(
    SELECT Name, `Ticket ID`, Channel
    FROM Tickets
    WHERE `Ticket Created at` >= '2017-09-01' AND
          `Ticket Created at` < '2017-10-01' AND
          Channel = 'Email' AND
         `First Agent Primary Email` = '[email protected]'
    ORDER BY RAND() LIMIT 5
) t3;

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

Don't use quote for columns name .. when you have composite name use backtics
use IN clause for several value (or repeat the column name for each or condition) and you can use limit 5 and order by rand() for get 5 casual row

  SELECT Name,`Ticket ID`,Channel
  FROM Tickets
  WHERE `Ticket Created at` between date('2017-09-01') AND date('2017-10-01')
  AND `Channel` IN ( 'Phone', 'Chat' , 'Email')
  AND `First Agent Primary Email` = '[email protected]'
  ORDER BY order by rand() limit 5

Upvotes: 0

areklipno
areklipno

Reputation: 528

your probably need that:

SELECT "Name","Ticket ID","Channel"
FROM Tickets
WHERE "Ticket Created at" between date('2017-09-01') AND date('2017-10-01')
AND ( "Channel" = 'Phone' or "Channel" = 'Chat' or "Channel" = 'Email' )
AND "First Agent Primary Email" = '[email protected]'
ORDER BY RAND() LIMIT 5;

Upvotes: 0

Markus Schreiber
Markus Schreiber

Reputation: 371

I think you look for something like this:

SELECT "Name","Ticket ID","Channel"
FROM Tickets
  WHERE "Ticket Created at" between date('2017-09-01') AND date('2017-10-01')
  AND "Channel" = 'Phone' or 'Chat' or 'Email'
  AND "First Agent Primary Email" = '[email protected]'
ORDER BY RAND() LIMIT 5;

as you can see i added the line ORDER BY RAND() LIMIT 5 which orders the rows randomly and returns the first 5.

Upvotes: 0

Related Questions