my image table id imagename category 1 abc nature 2 cdf animal .. .. .... .. .. .... can i use one query to select top 5 images from each category? I am using 1 query for each category now, right now, more and more category is added, and my site is really slow now
is it off the same table? just do a sub-select in your from's: Select ID, ImageName, Category, 0 AS FakeJoiner FROM (Select ID, ImageName, Category from Image Order By Datestamp Desc Limit 5) T1 On T1.FakeJoiner = FakeJoiner Inner Join (Select ID, ImageName, Category from Image Order By ImageName ASC Limit 5) T2 On T2.FakeJoiner = FakeJoiner Inner Join (Select ID, ImageName, Category from Image Order By ID Desc Limit 5) T3 On T3.FakeJoiner = FakeJoiner Inner Join (Select ID, ImageName, Category from Image Order By Datestamp Desc Limit 5) T4 On T4.FakeJoiner = FakeJoiner (Select ID, ImageName, Category from Image Order By Datestamp Desc Limit 5) T5 On T5.FakeJoiner = FakeJoiner Order By ImageName Or something like that you can join to the same table as many times as you want getting and 5 records that you want... you just need a joiner, or you could UNION the records, but I hate unions
yes from same table, yes this is solution,but later on if i need to add new category, then i need to change the sql query again. dont think this is good way to do it, but thanx anyway.
There is no good way to do this automatically. You can always use JOIN but it will not be well optimised.. Why don't you simply cache the results?
Create a UDF otherwise, to make the Query Dynamic. Then have the UDF create the SQL on teh fly and Exec SQL String. This is the only way or maybe can be done with stored proc but I'd go UDF for Dynamic SQL.