CODEWITHSUNDEEP
mysql
user3502374
user3502374

Reputation: 779

mysql more efficient way to query

below gets the job done but I feel like I am violating programming rule.

select *
from serverTable
where server like ‘proc1@server1’
order by reg_date desc
limit 1
union
select *
from serverTable
where server like ‘proc2@server1’
order by reg_date desc
limit 1
union
select *
from serverTable
where server like ‘proc3@server1’
order by reg_date desc
limit 1
union
select *
from serverTable
where server like ‘pro4@server1’
order by reg_date desc
limit 1
union
select *
from serverTable
where server like ‘proc5@server1’
order by reg_date desc
limit 1

Is there better way or correct way to get this done?

Upvotes: 4

Views: 50

Answers (3)

John
John

Reputation: 3996

Something like this should do it:

select
  serverTable.col1,
  serverTable.col2,
  serverTable.col3,
  max(reg_date)
from
  serverTable
where
  server in ('proc1@server1','proc2@server1','proc3@server1','proc4@server1','proc5@server1')
group by
  serverTable.col1,
  serverTable.col2,
  serverTable.col3
order by
  server, reg_date;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521419

One option would be to find the maximum reg_date for each server group, where server can take one of the 5 values in your UNION query, and then select the full records from your table for each server group.

SELECT t1.*
FROM serverTable t1
INNER JOIN
(
    SELECT server,
           MAX(reg_date) AS reg_date
    FROM serverTable
    WHERE server in ('proc1@server1', 'proc2@server1', 'proc3@server1', 'proc4@server1', 'proc5@server1')
    GROUP BY server
) t2
    ON t1.server = t2.server AND
       t1.reg_date = t2.reg_date

Upvotes: 1

Robert Columbia
Robert Columbia

Reputation: 6418

USE UNION ALL rather than UNION. By using UNION, the database is unnecessarily looking for duplicates that don't exist. All of your UNIONed queries include a WHERE clause like "server like (some unique value)". server can't be equal to more than one of these, so you won't ever have any duplicates to filter out.

Also, use = instead of like. LIKE is used when you want to do a wildcard search, which you are not doing.

Upvotes: 0

Related Questions