CODEWITHSUNDEEP
mysqljoinsubquery
user3454116
user3454116

Reputation: 184

Creating view on top of a subquery in mysql

I have a query which fetches the max date time from on column and also the corresponding data of the other columns from the table. But for selecting the max date time I have to use a subquery so that i get the corresponding value of other columns. But since I have used a subquery with an alias I am unable to make a view on top of that. So can my query be modified to give me my desired result and also I am able to make my view with that query.

The sql query which I have made for my requierment is below.It gives me the desired result but I am unable to make a view on top of the query.Hence I am forced to make to separete views to get the desired result. Can the query be modified to give me the result and also I am able to make the view on top of the query.

SELECT SD.a_n, SD.t_c, SD.ppl, date_time FROM (SD JOIN (SELECT SD.t_c, MAX(date_time) AS MDT FROM SD GROUP BY t_c)wn_m_d_t ON (((SD.t_c = wn_m_d_t.t_c) AND (SD.date_time = wn_m_d_t.MDT)))) WHERE ((SD.t_c IN('drep','sred')))

Upvotes: 0

Views: 86

Answers (2)

lopo
lopo

Reputation: 306

I think this query is possible without using a subquery too.

SELECT SD.a_n, SD.t_c, SD.ppl, SD.date_time
FROM SD
LEFT JOIN SD AS sd2
 ON sd2.t_c = SD.t_c
 AND sd2.date_time > SD.date_time   
WHERE SD.t_c IN ('drep', 'sred') AND sd2.t_c IS NULL

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Your query in a bit more readable format is:

SELECT SD.a_n, SD.t_c, SD.ppl, date_time
FROM SD JOIN
     (SELECT SD.t_c, MAX(date_time) AS MDT
      FROM SD
      GROUP BY t_c
     ) wn_m_d_t
     ON SD.t_c = wn_m_d_t.t_c AND SD.date_time = wn_m_d_t.MDT
WHERE SD.t_c IN ('drep', 'sred');

MySQL has a string restriction that views cannot contain subqueries in the from clause. You seem to want to get the records in SD with the most recent date for each t_c. Here is another approach:

SELECT SD.a_n, SD.t_c, SD.ppl, SD.date_time
FROM SD
WHERE SD.t_c IN ('drep', 'sred') AND
      NOT EXISTS (select 1
                  from sd sd2
                  where sd2.t_c = sd.t_c and
                        sd2.date_time > sd.date_time
                 );

And, this will work in a view. Subqueries are allowed in the select and from clauses. This says, "Get me all rows in SD where there is no record with the same t_c and a bigger date_time."

Upvotes: 2

Related Questions