CODEWITHSUNDEEP
mysql
N_F_S
N_F_S

Reputation: 109

Mysql Distinct while selecting other column

SELECT DISTINCT ID FROM (SELECT * FROM tbl1 UNION 
SELECT * FROM tbl2 UNION 
SELECT * FROM tbl3 UNION 
SELECT * FROM tbl4 ORDER BY total_hits DESC) AS sumtbl LIMIT 50;

This query works fine and selects unique ID's ordered by total_hits DESC, the question is how can I return total_hits column too having Id's unique?

Upvotes: 1

Views: 472

Answers (3)

WattsInABox
WattsInABox

Reputation: 4636

If you want the total_hits FROM all the ids across all the tables, you'll need to do a sum / group by. Not sure if this is what you're asking since the question is vague...

SELECT DISTINCT id, sum(total_hits) total_hits
FROM (SELECT * FROM tbl1 UNION 
     SELECT * FROM tbl2 UNION 
     SELECT * FROM tbl3 UNION 
     SELECT * FROM tbl4) AS sumtbl 
GROUP BY id
ORDER BY total_hits DESC
LIMIT 50

Also, don't use select * as it's bad practice, esp. if you go to add a column to one of those tables and not the others, your whole query will break.

Upvotes: 0

a1ex07
a1ex07

Reputation: 37354

UPDATE

In your case you can do 

SELECT ID, MAX(total_hits) as max_hits
FROM (
   SELECT * FROM tbl1 UNION 
   SELECT * FROM tbl2 UNION 
   SELECT * FROM tbl3 UNION 
   SELECT * FROM tbl4
)sumtbl
GROUP BY ID
ORDER BY max_hits DESC
LIMIT 50

Note : you don't need ORDER BY in derived query, it goes to upper

Also, it would be better, if you
SELECT id, total_hits
FROM tbl1 UNION ALL
SELECT id, total_hits FROM tbl2 etc. rather then selecting all fields from tables in your derived query.

Upvotes: -1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171391

SELECT ID, SUM(total_hits) 
FROM (
    SELECT * FROM tbl1 UNION 
    SELECT * FROM tbl2 UNION 
    SELECT * FROM tbl3 UNION 
    SELECT * FROM tbl4
) AS sumtbl
GROUP BY ID
ORDER BY SUM(total_hits) DESC
LIMIT 50;

Upvotes: 2

Related Questions