[SOLVED] Controlling how GROUP BY groups columns

[flash gordon]

First off, please forgive me for not being able to find what I need on Google. I know I'm not the first person to ask this, I just can't find the answer....so

 

I have an sql statement like so:

"SELECT id, tid, asset FROM assets

WHERE id IN(1,2,3)

AND tid IN ($tid,0)

GROUP BY id";

 

The problem is that my tid gets grouped in a way I don't want. Pretty much I want to be able to run a query where fetches all assets with a tid of "x" or if it doesn't exist use the default of "0". My sql groups all items into tid's of 0 and not favoring the $tid.

 

Any idea what I need?

Thank you.

[DavidAM]

If you are saying that there will be a row with tid of "x" and another row with tid of "0" for the same id, and you don't want the row with tid = 0 unless the row with tid = x does not exist, then I think I would try something like this:

 

SELECT id, tid, asset FROM assets a1
WHERE id IN(1,2,3)
AND ( tid = $tid 
    OR (tid = 0
        AND NOT EXISTS (SELECT * FROM assets a2 WHERE a2.id = a1.id and a2.tid = $tid)))
GROUP BY id

 

Although that looks ugly, so there must be a better way.

[fenway]

Your SQL query is behaving exactly as you asked it to.

 

You asked your query to GROUP BY id -- so it did.  That means any other non-dependent column value is meaningless.

 

Show us some sample data.

[flash gordon]

Thanks for the responses, guys. here's a table

 

table: assets
assetid | tid | name | asset
------------------------------
1         |  0  | image | foobar.jpg
2         |  0  | pdf    | default.pdf
1         |  1  | image | specific.jpg
-------------------------------

 

The result of my query would be to return assetid 1 tid 1 if possible. If not, return assetid 1 tid0 as the default content.

 

Make sense? Perhaps it's a bad database structure. If that's the case, please suggest a structure and perhaps give a reason as well....so I can learn and not make the same mistake again.

 

Thanks for the attention to this. Your help and time is greatly appreciated!

[anatak]

It would help if you explain what you are trying to do.

but maybe you could just order by ?

 

"SELECT id, tid, asset FROM assets

WHERE id IN(1,2,3)

AND tid IN ($tid,0)

ORDER BY id ASC, tid DESC";

 

and then check with php if the first tid value of a newid is 0 or 1

 

[flash gordon]

hmmm...i think all of you guys are right. Perhaps I'm really just should be doing two queries or one query/sub-query. And yeah, if I'm grouping the other columns are meaningless...perhaps I should rethink a couple of things.

 

But let me get a final answer to my question: If I use GROUP BY, can I control how other column values get flatted. For instance:

<i>"SELECT assetid, asset FROM assets

WHERE assetid = 1

GROUP BY id";</i>

Can I control how the asset column will be flatted to make it favor specific.jpg over foobar.jpg or because I'm doing a GROUP BY, the asset colum should be irrelative?

 

Thanks again guys!

[anatak]

if you want you can do an ORDER BY to favor what you want and then a GROUP BY ( I think)

ORDER BY assetid DESC

GROUP BY id

 

try it and see if this works

[kickstart]

Hi

 

Looks to me that you want the max tid:-

 

SELECT id, tid, asset

FROM assets a

INNER JOIN (SELECT id, MAX(tid) AS MaxTid FROM assets WHERE tid IN ($tid,0) GROUP BY id) b

ON a.id = b.id

AND a.tid = b.MaxTid

WHERE a.id IN(1,2,3)

 

All the best

 

Keith

[flash gordon]

Cool guys! Thanks. I think I have what I need.

 

Cheers