Need help constructing MySQL Query

Question

My data & table is as follows:

+------+------------+-----------+--------------+------------+----------+-------------+
| sr   | thread_id  | thread_no | client_name  | date       | time     | status      |
+------+------------+-----------+--------------+------------+----------+-------------+
|  871 | 1281473902 |         0 | Demo Company | 2011-01-17 | 16:53:00 | Closed      |
| 1179 | 1269343346 |         0 | Demo Company | 2010-03-23 | 11:22:00 | Open        |
| 1180 | 1269343585 |         0 | Demo Company | 2010-03-23 | 11:26:00 | Closed      |
| 1181 | 1269343679 |         0 | Demo Company | 2010-03-23 | 11:27:00 | Open        |
| 1182 | 1269343835 |         0 | Demo Company | 2010-03-23 | 11:30:00 | Open        |
| 2392 | 1295285762 |         0 | Demo Company | 2011-01-17 | 17:35:00 | Closed      |
| 2393 | 1295286578 |         0 | Demo Company | 2011-01-18 | 08:01:00 | Open        |
| 2526 | 1299764352 |         0 | Demo Company | 2011-04-29 | 11:01:00 | Closed      |
| 8727 | 1299764352 |         1 | Demo Company | 2011-06-16 | 09:31:00 | Closed      |
| 8728 | 1299764352 |         2 | Demo Company | 2011-06-16 | 09:56:00 | Closed      |
| 8729 | 1299764352 |         3 | Demo Company | 2011-06-16 | 09:57:00 | Closed      |
| 8731 | 1308234742 |         0 | Demo Company | 2011-06-16 | 10:31:00 | Open        |
| 8734 | 1308236479 |         0 | Demo Company | 2011-06-16 | 11:00:00 | Open-Urgent |
| 8735 | 1308236519 |         0 | Demo Company | 2011-06-16 | 11:01:00 | On Hold     |
| 8736 | 1308236519 |         1 | Demo Company | 2011-06-16 | 11:02:00 | Closed      |
| 8740 | 1308242129 |         0 | Demo Company | 2011-06-16 | 12:34:00 | Closed      |
| 8796 | 1308242129 |         1 | Demo Company | 2011-06-24 | 06:27:00 | Closed      |
| 8798 | 1308242129 |         2 | Demo Company | 2011-06-24 | 07:43:00 | Open        |
| 8799 | 1308242129 |         3 | Demo Company | 2011-06-24 | 07:44:00 | Open        |
+------+------------+-----------+--------------+------------+----------+-------------+

I need to select ROWS with DISTINCT thread_id and having the highest value of thread_no for that distinct thread_id

For example, for thread_id 1299764352, I need to select the row with sr=8729

The closes I could come to was with this query:

SELECT sr, thread_id, max(thread_no) as thread_no, client_name, date, time, status 
    FROM table 
    group by thread_id 
    order by sr

But this query gives me the row with thread_no=0 and not the row with highest value of thread_no

I hope I was able to explain well.

Please help.

Thx Vai

Shef · Accepted Answer · 2011-06-24 13:37:48Z

5

I guess the following query is what you need:

SELECT * FROM table AS a
WHERE NOT EXISTS (
SELECT * FROM table AS b 
WHERE b.thread_id = a.thread_id AND b.thread_no > a.thread_no )

edited Jun 24, 2011 at 13:37

Shef

45.6k15 gold badges82 silver badges91 bronze badges

answered Jun 24, 2011 at 13:24

Grigor Gevorgyan

6,8934 gold badges38 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ypercubeᵀᴹ Over a year ago

you can make your code look like code by selecting it and clicking on the {} button.

Grigor Gevorgyan Over a year ago

So wouldn't you be so kind to approve my answer ? :)

Shef · Accepted Answer · 2011-06-24 13:22:25Z

2

Try:

SELECT * FROM table 
GROUP BY thread_id 
ORDER BY MAX(thread_no) DESC, sr ASC

answered Jun 24, 2011 at 13:22

Shef

45.6k15 gold badges82 silver badges91 bronze badges

2 Comments

vai Over a year ago

solution provided by @Grigor Gevorgyan worked perfect. Thx @Grigor Gevorgyan

Shef Over a year ago

@vai: Whichever solution works for you. Check if it works in the first place, then check for speed differences. ;)

Stef Heyenrath · Accepted Answer · 2011-06-24 13:30:21Z

1

I guess you're looking for the analytic function 'partition by'. See this article.

answered Jun 24, 2011 at 13:30

Stef Heyenrath

9,97014 gold badges73 silver badges132 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:55:52Z

1

SELECT m.* 
FROM myTable AS m
  JOIN
    ( SELECT thread_id
           , MAX(thread_no) AS max_no
      FROM myTable
      GROUP BY thread_id
    ) AS grp
    ON m.thread_id = grp.thread_id
    AND m.thread_no = grp.max_no

or like this (credits to Quassnoi):

SELECT m.* 
FROM 
    ( SELECT DISTINCT thread_id
      FROM myTable
    ) AS md
  JOIN myTable AS m
    ON m.sr =
      ( SELECT mi.sr
        FROM myTable mi
        WHERE mi.thread_id = md.thread_id
        ORDER BY mi.thread_no DESC
        LIMIT 1
      )

edited May 23, 2017 at 11:55

CommunityBot

11 silver badge

answered Jun 24, 2011 at 13:30

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Collectives™ on Stack Overflow

Need help constructing MySQL Query

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related