1

My query in PHP consist of two parts: creating temporary table and the main query.

How should I write this query in PHP?

//Creating temporary table part:

create temporary table mytable as(SELECT `Name`, `Date`, `Condition` 
FROM info
ORDER BY  `Date` DESC );

//The main query part:

select mytable.Name, mytable.Date, mytable.Condition FROM mytable 
right join info on mytable.Name = info.Name
Group by mytable.Name;

Edited: My query contain 3 columns(Name, Date and Condition). I need the rows with max(Date), since I have some rows with the same Name, I should use Group.

The following query does not help:

SELECT `Name`, max(Date), `Condition` 
FROM info
Group by Name
ORDER BY  min(Date) DESC
nazanin
  • 73
  • 2
  • 14
  • 3
    Why do you need a temp table at all? – juergen d Nov 17 '14 at 00:01
  • especially if you are joining onto the original table and not restricting anything. You can join a table onto itself. – Marshall Tigerus Nov 17 '14 at 00:03
  • @juergend, Only column Name should be DISTINCT also I want column Date to be Desc. "Select Column1, Column2, Column3 From Table Group By Column1 Order By Column2 DESC" dose not help me since it first group by and then do ordering. do you have any other idea? – nazanin Nov 17 '14 at 00:10
  • "also I want column Date to be Des" --- tables are unordered structures. So the order is not kept. Hence `ORDER BY` there has no any influence – zerkms Nov 17 '14 at 00:14
  • @Marshall, I don't know very much about database, would you please explain more? – nazanin Nov 17 '14 at 00:15
  • @zerkms, ORDER BY in my example makes sense. My data must be sorted first according to Date column and then group by Name. – nazanin Nov 17 '14 at 00:24
  • @nazanin: **TABLES ARE UNORDERED STRUCTURES**. It **DOES NOT** and **MUST NOT** keep the same order you used for your inserts. So, `ORDER BY` does not make any sense. – zerkms Nov 17 '14 at 00:26
  • @zerkms: All right, do you have any solution for writing a query in my case? – nazanin Nov 17 '14 at 00:30
  • @nazanin I don't: you haven't explained what you need your query to do. – zerkms Nov 17 '14 at 00:31
  • @nazanin see http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql and http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group – zerkms Nov 17 '14 at 00:47

0 Answers0