SQL select id from a table to query again all at once

Question

Given the following table:

id message        owner_id counter_party_id datetime_col
1  "message 1"    4        8                2014-04-01 03:58:33
2  "message 2"    4        12               2014-04-02 10:27:34
3  "message 3"    4        8                2014-04-03 09:34:38
4  "message 4"    4        12               2014-04-06 04:04:04

How to get the most recent counter_party number and then get all the messages from that counter_party id?

output:

2  "message 2"    4        12            2014-04-02 10:27:34
4  "message 4"    4        12            2014-04-06 04:04:04

I think a double select must work for that but I don't know exactly how to perform this.

Thanks

which dbms .. Mysql or Sqlserver ? what have u tried ? – Mudassir Hasan Apr 07 '14 at 10:43 — Mudassir Hasan, Apr 07 '14 at 10:43

score 3 · Answer 1 · answered Apr 07 '14 at 10:43

A number of ways exists to do that. This is properly the most straight forward.

SELECT *
FROM YourTable
WHERE counter_party_id = (SELECT MAX(counter_party_id) FROM YourTable);

You could also select the MAX into a variable before hand;

DECLARE @m int
SET @m = (SELECT MAX(counter_Party_id) FROM YourTable);

And use @m in your where.

Depending on which database system you're using other tools exists which can help you as well.

score 2 · Answer 2 · answered Apr 07 '14 at 10:44

2

You can get the max counter_party_id:

SELECT * 
FROM table 
WHERE counter_party_id = (
                           SELECT MAX(counter_party_id) 
                           FROM table
                         )

answered Apr 07 '14 at 10:44

majidarif

18,694
16
88
133

score 2 · Answer 3 · edited Apr 07 '14 at 11:40

2

You can use sub-query to get most recent country_party_id by first ordering the list by datetime_col and then filtering the first country_party_id from it. Here is the query.

SELECT * FROM counter_party_id WHERE counter_party_id = (SELECT TOP 1 counter_party_id FROM counter_party_id ORDER BY datetime_col DESC)

edited Apr 07 '14 at 11:40

BenMorel

34,448
50
182
322

answered Apr 07 '14 at 11:21

Diwakar Mishra

29
2

score 1 · Answer 4 · answered Apr 07 '14 at 10:46

If you are using SQL Server try first method otherwise use second method

Try this

   SELECT * FROM 
    (
      SELECT *,Row_Number() Over(Partition By counter_party_id  Order By datetime_col DESC) RN
      FROM YourTable
    )
    WHERE RN = 1

or:

   SELECT * FROM YourTable S JOIN
     (
      SELECT counter_party_id,Max(datetime_col) FROM YourTable Group By counter_party_id 
     )T ON S.datetime_col = T.datetime_col

score 0 · Answer 5 · answered Apr 07 '14 at 10:46

0

 SELECT MAX(counter_party_id ) AS counter_party_id,message,owner_id,datetime_col 
 FROM table;

answered Apr 07 '14 at 10:46

Madara Uchiha

128
7

score 0 · Accepted Answer · answered Apr 07 '14 at 11:38

Should be the last message so either max(id) or latest datetime in this case, counter_party_id is just an user id the most recent counter_party_id does not mean the max counter_party_id(I found the solution in the answers and I gave props):

SELECT * 
FROM yourTable 
WHERE counter_party_id = ( SELECT MAX(id) FROM yourTable )

or

SELECT * 
FROM yourTable 
WHERE counter_party_id = ( SELECT counter_party_id FROM yourTable ORDER BY m.time_send DESC LIMIT 1)

Reason being is that I simplified the example but I had to implement this in a much more complicated scheme.

SQL select id from a table to query again all at once

6 Answers6