CODEWITHSUNDEEP
mysql
Kristian Rafteseth
Kristian Rafteseth

Reputation: 2032

how to select rows based on two distinct columns?

I have a table like this

id  type        type_id cust    ip_address      datetime
395 Category    5       0       119.63.196.29   2012-11-27 00:34:21
394 Cart        812     0       84.208.217.178  2012-11-27 00:31:48
393 Cart        812     0       84.208.217.178  2012-11-27 00:31:41
392 Cart        812     0       84.208.217.178  2012-11-27 00:31:35
391 Product     812     0       84.208.217.178  2012-11-27 00:31:34

i want to select 4 rows, ordered by id desc, which have distinct type+type_id data.

so the result from the query would remove id 393 and 392, because type and type_id combination is already in id 394.

pseudocode: select * from table where type and type_id is distinct from resultset, order by id desc limit 4

Upvotes: 0

Views: 1350

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

Try this:

SELECT t1.* 
FROM table t1
INNER JOIN 
(
   SELECT MAX(id) maxid, type_id, type
   FROM table
   GROUP BY type_id, type
) t2 ON t1.id = t2.maxid AND t1.type = t2.type AND t1.type_id = t2.type_id
ORDER BY t1.id desc 
LMIT 4;

SQL Fiddle Demo

Upvotes: 0

Jonathan Leffler
Jonathan Leffler

Reputation: 753475

SELECT *
  FROM AnonymousTable AS a1
  JOIN (SELECT type, type_id, MAX(id) AS id
          FROM AnonymousTable
         GROUP BY type, type_id
       ) AS a2
    ON a1.type = a2.type AND a1.type_id = a2.type_id AND a1.id = a2.id

Though, since the ID values are presumably unique, you can simplify the sub-query and subsequent join:

SELECT *
  FROM AnonymousTable AS a1
  JOIN (SELECT MAX(id) AS id
          FROM AnonymousTable
         GROUP BY type, type_id
       ) AS a2
    ON a1.id = a2.id

The longer form will produce the right answer even if id is not unique (as long as the combination of id, type and type_id is unique).

Upvotes: 0

juergen d
juergen d

Reputation: 204746

select * from table
where id in 
(
  select min(id)
  from table 
  group by  type, type_id 
)
order by id desc
limit 4

Upvotes: 3

Related Questions