List:General Discussion« Previous MessageNext Message »
From:Tao Liang Date:March 5 2000 7:35pm
Subject:group by conditions
View as plain text  
Hi,

Is there a way to specify the conditions of a "GROUP BY" clause?  i.e., use
first occurrence, or use the record with the latest creation time, etc.

I am trying to make a query which left joins two tables.

SELECT t1.*, t2.*, 100*(t1.B-t2.C)/t2.C as percentage
FROM table1 AS t1 LEFT JOIN table2 AS t2
ON t1.A=t2.A
GROUP BY t1.A
ORDER BY percentage DESC
LIMIT 20

t1.A is unique.  t2.A is not unique.  So if I do the query without GROUP BY
I have duplicate A with different values.  But if I do it with GROUP BY I
only get the earliest entry of t2.A.

example,
with GROUP BY I get
ID1 X Y Z
ID2 M N O
ID3 A B C

without GROUP BY I get
ID1 I J K
ID1 E F G
ID1 X Y Z
ID2 R S T
ID2 M N O
ID3 U V W
ID3 A B C

What I want is a table with unique column A and the latest record, ordered
by some parameter (percentage)
ID1 I J K
ID2 R S T
ID3 U V W

Tao

Thread
AUTO_INCREMENT Limits to 256 recordsKevin P. O'Brien11 Mar
  • Re: AUTO_INCREMENT Limits to 256 recordsMicheal Mc Evoy11 Mar
  • one query for two identical tablesTao Liang3 Mar
    • RE: one query for two identical tablesNicolas Prade3 Mar
  • group by conditionsTao Liang5 Mar
    • Re: group by conditionssinisa6 Mar
      • accessing db while updating itTao Liang26 Mar
Re: AUTO_INCREMENT Limits to 256 recordsKevin P. O'Brien11 Mar
  • Re: AUTO_INCREMENT Limits to 256 recordsRobert Hazeltine11 Mar
Re: AUTO_INCREMENT Limits to 256 recordsKevin P. O'Brien11 Mar
  • select count distinctChinaConnect11 Mar
    • Re: select count distinctChris12 Mar
    • Re: select count distinctScott Liu13 Mar
      • Re: select count distinctMichael Widenius15 Mar
  • Re: AUTO_INCREMENT Limits to 256 recordsChristian Mack12 Mar
Re: AUTO_INCREMENT Limits to 256 recordsKevin P. O'Brien11 Mar
Re: AUTO_INCREMENT Limits to 256 recordsEd Carp12 Mar
Re: AUTO_INCREMENT Limits to 256 recordsEd Carp13 Mar
Re: Bad handshake problem with java and MySQL 3.22.32Carlos Proal15 Feb