MySQL Forums
Forum List  »  Microsoft SQL Server

need help w/ query..
Posted by: Jaime Reza
Date: November 02, 2004 01:32PM

I have a query

select p.* from person p
where p.person_name in (select top 2 person_name from events group by person_name order by count(person_name) desc)
order by p.person_name

based on two tables

table person
person_name PRIMARY KEY,

table events
eid int PRIMARY KEY,

I cant use TOP specifier in mysql and cant use LIMIT because it has to work in both

The results are limited to what is in the events table
so I did a left join on the limiting table events instead..
but I cant get the top 2 with the highest counts, only the
top 2 that appear in the table events..

select p.esn,p.address
FROM events AS E1
LEFT JOIN events AS E2
on E2.eid > E1.eid, person p
WHERE p.person_name = E1.person_name
GROUP BY E1.eid, p.person_name,p.address
HAVING (COUNT(E2.sid) < 2)
ORDER BY p.person_name ASC

based on

select E1.person_name
FROM events AS E1
LEFT JOIN events AS E2
on E2.eid > E1.eid
GROUP BY E1.eid,E1.person_name
HAVING (COUNT(E2.sid) < 2)

any ideas?

Options: ReplyQuote

Written By
need help w/ query..
November 02, 2004 01:32PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.