-1

My sample query:

SELECT User.id, User.name, MAX(Track.id) as track_id 
FROM users AS User 
    LEFT JOIN tracks AS Track ON (User.id = Track.user_id) 
GROUP BY Track.id

Can i alias MAX(Track.id) to "fit" it in Track? E.g. something like MAX(Track.id) as Track.id So i can than do ORDER BY Track.id.

I need that because i already have all APP logic with dynamic ORDER and WHERE and only in one case i need to do GROUP BY, also i do all that stuff using ORM. So i want to leave all APP logic as is but in that particular case add aggregate field and GROUP BY and in my APP still use Track.id in meaning MAX(Track.id) is it possible?

Yaroslav
  • 2,338
  • 26
  • 37
  • 1
    You better explain what you want your query to do, since your question doesn't correlate with SQL principles too much. – zerkms Nov 18 '14 at 00:20
  • I want to select users and their latest track_id – Yaroslav Nov 18 '14 at 00:22
  • `MAX(Track.id)` --- that's how you select the "latest" value. And it's already in your query. – zerkms Nov 18 '14 at 00:22
  • Relevant though: http://stackoverflow.com/q/1313120/251311 http://stackoverflow.com/q/3800551/251311 http://stackoverflow.com/q/9192673/251311 – zerkms Nov 18 '14 at 00:24
  • yes, i know it. Than i want to alias it to use in APP as Track.id, because it's only one case, in other cases i will select user and all tracks without `group by` and i don't want to rewrite all calls to `Track.id` in all my APP – Yaroslav Nov 18 '14 at 00:24
  • 1
    " Than i want to alias it to use in APP as Track.id" --- `track.id` is a real column name. You cannot "alias to it". That's why I asked to tell about the *real* issue, not about your broken "solution" – zerkms Nov 18 '14 at 00:25
  • It's what my question was: can i substitute real column value to computed one or not ant only in runtime. – Yaroslav Nov 18 '14 at 00:26
  • 1
    You technically can create a view and make it looking like a table with values calculated in runtime. But I still think you are solving a wrong problem (you don't want to share it though) - since your query is broken by design: `Track.name` is not what you think. – zerkms Nov 18 '14 at 00:27
  • I'm already share my problem: i'm already have my app working. And i widely use that column (`Track.id`). But now in one case (if user select checkbox) i need to do group by `Track.id` and show only last one. So if i will add `MAX(Track.id) as track_id` field i than need to rewrite all calls to `Track.id` in my application to check `isset($response['track_id'])?$response[track_id]:$response['Track']['id']`. I don't want to do that. – Yaroslav Nov 18 '14 at 00:32
  • 1
    first thing: your query is wrong. `Track.Name` is a **random** name, not the name from the latest track. Next - what explodes the result set into nested arrays? How you know that `Track` table has an `id` column? Is there some mapping for it? If so - how it would distinguish between `user.id` and `track.id` which in result set would be returned as `id`? (only column names/aliases are kept in result set) – zerkms Nov 18 '14 at 00:34
  • Newermind Track.name. It's simplified query. My actual query much more complicated. I have my question already answered. If i can't substitute real value by computed one i will go the other way. Thanks. – Yaroslav Nov 18 '14 at 00:39
  • It all done by ORM and will become `$response['User']['id']` and `$response['Track']['id']` – Yaroslav Nov 18 '14 at 00:41

1 Answers1

1

Use backtick(`) in naming alias or in selecting column like this

SELECT User.id, User.name, Track.name, MAX(Track.id) as `Track.id` 
from users as User 
LEFT JOIN tracks as Track ON (User.id = Track.user_id) 
GROUP BY Track.id

backtick are uses to escape MySQL reserved words.

CodeSlayer
  • 1,318
  • 1
  • 12
  • 34