Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tumbleweed
Starting Member
1 Post |
 Posted - 2011-12-12 : 06:04:59
|
Hi there,i have the following sql which just doesnt seem to pick up on the order by part:SELECT FIELD_OPTION_CHILDTABLE.OPTION_TRANSLATION FROM FIELD_OPTION_CHILDTABLE WHERE FIELD_OPTION_CHILDTABLE.CONTENTID in (select TOP 50 PHRASE_TO_APPEAR_ON_LABEL from CT_PRODUCT_DATA1_LABEL_PHRASES Join contentstatus On CT_PRODUCT_DATA1_LABEL_PHRASES.contentid = contentstatus.contentid where CT_PRODUCT_DATA1_LABEL_PHRASES.revisionid = contentstatus.currentrevision and CT_PRODUCT_DATA1_LABEL_PHRASES.contentid = '278381' GROUP BY PHRASE_TO_APPEAR_ON_LABEL ORDER BY MAX(SORTRANK) ASC) and OPTION_LANGUAGE = 'GB' and OPTION_TRANSLATION not like '%*%'[code] Any advice greatly appreciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-12 : 10:28:03
|
which order by you're expecting it to pick up? As of now it has only order by in inner query and thats used for getting top 50 items alone. Its just used as a lookup for outer query and results from main query will be based on that. Since there's no order by specified in outer query you cant guarantee the order or retrieval of results from main query unless you give an explicit order by SELECT FIELD_OPTION_CHILDTABLE.OPTION_TRANSLATION FROM FIELD_OPTION_CHILDTABLE WHERE FIELD_OPTION_CHILDTABLE.CONTENTID in (select TOP 50 PHRASE_TO_APPEAR_ON_LABEL from CT_PRODUCT_DATA1_LABEL_PHRASES Join contentstatus On CT_PRODUCT_DATA1_LABEL_PHRASES.contentid = contentstatus.contentid where CT_PRODUCT_DATA1_LABEL_PHRASES.revisionid = contentstatus.currentrevision and CT_PRODUCT_DATA1_LABEL_PHRASES.contentid = '278381' GROUP BY PHRASE_TO_APPEAR_ON_LABEL ORDER BY MAX(SORTRANK) ASC) and OPTION_LANGUAGE = 'GB' and OPTION_TRANSLATION not like '%*%'ORDER BY <some column here> ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
group by order by max