Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135

    Unanswered: is it possible to order by sub-squery?

    Hi all,

    New to SQL so was hoping you could clarify if it is it possible to order by sub-squery? as i try this:

    select
    pss_store_code
    ,store_name
    ,pss_story_id
    ,st_title
    From
    pgm_store_stories as ss
    inner join pgm_stories as st
    on ss.pss_story_id = st.st_id
    inner join stores as s
    on s.store_code = ss.pss_store_code
    Where pss_story_id > '7829'

    (
    SELECT store_code
    FROM stores
    WHERE storeStatus = 'ON'
    )
    ORDER BY
    (
    SELECT st_title
    FROM pgm_stories
    )

    but get this error?

    (16382 row(s) affected)
    Msg 512, Level 16, State 1, Line 15
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    (0 row(s) affected)

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Not really sure what you want. But this will work:
    Code:
    select
    	pss_store_code
    	,store_name
    	,pss_story_id
    	,st_title
    From
    	pgm_store_stories as ss
    		inner join pgm_stories as st
    			on ss.pss_story_id = st.st_id
    		inner join stores as s
    			on s.store_code = ss.pss_store_code
    Where pss_story_id > '7829'
    	and s.storeStatus = 'ON'
    ORDER BY st.st_title
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi Wim, thank you for the reply.

    Sorry i should have said.

    The scenario is this: i have 214 shops that are all set a task "to do" (the st_title), so i need to produce a list that replicates the following:

    shop 1 = to do 1 (st_title 1) actioned = yes
    to do 2 (st_title 2) actioned = yes
    to do 3 (st_title 3) actioned = NO
    to do 4 (st_title 4) actioned = yes

    Shop 2 = to do 1(st_title 1) actioned = yes
    to do 2 (st_title 2) actioned = yes
    to do 3 (st_title 3) actioned = yes
    to do 3 (st_title 4) actioned = yes


    Shop 3 = to do 1(st_title 1) actioned = NO
    to do 2 (st_title 2) actioned = NO
    to do 3 (st_title 3) actioned = yes
    to do 3 (st_title 4) actioned = yes
    etc...to 214 shops

    at the moment the list is more like:

    shop 1 = to do 1 (st_title 1),
    shop 2 = to do 1 (st_title 1),
    shop 3 = to do 1 (st_title 1) ...to 214 shops, and then the next one begins
    shop 1 = to do 2 (st_title 2),
    shop 2 = to do 2 (st_title 2),
    shop 3 = to do 2 (st_title 2) ...to 214 shops, and so on


    I think I may be a little way away with my query as im new to SQL, but im trying to find a solution.

    Any ideas would be great

    Kind regards
    MG

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this
    Code:
    SELECT store_code, 
    	0 as sequenceNr, 
    	'Shop ' + store_name + ' = ' as OutputLine
    FROM stores
    WHERE storeStatus = 'ON'
    	UNION ALL
    SELECT 
    	s.store_code,
    	pss_story_id as sequenceNr, 
    	'do ' + st_title + ' actioned = ' + CASE WHEN actioned = 'Y' THEN 'yes' ELSE 'no' END as OutputLine
    From
    	pgm_store_stories as ss
    		inner join pgm_stories as st
    			on ss.pss_story_id = st.st_id
    		inner join stores as s
    			on s.store_code = ss.pss_store_code
    Where pss_story_id > '7829'
    ORDER BY 1, 2
    The output will be :
    Code:
    Shop n = 
    to do <st_title 1> actioned = NO
    to do <st_title 2> actioned = NO
    to do <st_title 3> actioned = yes
    ...
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    hi thanks for the reply.

    I will try this and check back,

    Question though, i need to learn more on SQL as my knowledge still isnt the best, and i was wondering if you knew of a website where i could read up. specifcally how you done things like the ELSE, + signs etc, what these are, do etc.

    I've gone through w3school

    Regards
    MG
    Last edited by mind_grapes; 12-04-09 at 07:47.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

  7. #7
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi Wim,

    That worked an brought back all the things to do, so thank you.

    One small request though, how would i change it to bring back a list with only the outstanding things to do, with a total? so :

    Shop n =
    to do <st_title 1> actioned = NO
    to do <st_title 2> actioned = NO
    total = 2

    Been trying to play around with it, but im struggling. Here is my amended version, in red(which doesnt work):

    Code:
    SELECT store_code, Store_Name,
    	0 as sequenceNr, 
    	'Shop ' + store_name + ' = ' as OutputLine
    FROM stores 
    WHERE storeStatus = 'ON' 
    	UNION ALL
    SELECT 
    	s.store_code,s.Store_Name,
    	pss_story_id as sequenceNr, 
    	 st_title + ' st_status = ' + CASE WHEN st_status = '1' THEN 'yes' ELSE 'no' END as OutputLine
    From
    	pgm_store_stories as ss
    		inner join pgm_stories as st
    			on ss.pss_story_id = st.st_id
    		inner join stores as s
    			on s.store_code = ss.pss_store_code
    Where st_release_date > '11-11-2009' AND store_code NOT IN
    	(
    		select ar_store_code
    		from pgm_action_responses
    		
    	)
    ORDER BY 1, 2
    But just to say once agin, everything else is awesome, so thank you.

    Any pointers would be appreciated.

    Kind regards
    MG
    Last edited by mind_grapes; 12-07-09 at 09:37. Reason: forgot to include some code

  8. #8
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    hi all this has been modified to thi:

    Code:
    select A.* from (
    
    SELECT store_code, Store_Name,
    0 as sequenceNr,
    'Shop ' + store_name + ' = ' as OutputLine
    FROM stores
    WHERE storeStatus = 'ON' and store_code NOT IN
    (
    select ar_store_code
    from pgm_action_responses
    )
    UNION ALL
    SELECT
    s.store_code,s.Store_Name,
    pss_story_id as sequenceNr,
    st_title + ' st_status = ' + CASE WHEN st_status = '1' THEN 'yes' ELSE 'no' END as OutputLine
    From
    pgm_store_stories as ss
    inner join pgm_stories as st
    on ss.pss_story_id = st.st_id
    inner join stores as s
    on s.store_code = ss.pss_store_code
    Where pss_story_id > '7829'
    )A
    
    where right(rtrim(A.outputline),2) = "no"
    
    Order by 1, 2
    But for some reason, pss_story_id doesnt bring back any ids above 7831, but i know there are some? any ideas guys ?

    Regards
    MG

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try
    Code:
    SELECT DISTINCT
    	s.Store_Name,
    	0 as sequenceNr,
    	'Shop ' + s.store_name + ' = ' as OutputLine
    From pgm_store_stories as ss
    	inner join pgm_stories as st
    		on ss.pss_story_id = st.st_id
    	inner join stores as s
    		on s.store_code = ss.pss_store_code
    Where s.storeStatus = 'ON' and 
    	ss.pss_story_id > '7829' AND
    	NOT(st_status = '1') AND
    	st_release_date > '2009-11-11' AND -- YYYY-MM-DD format
    	store_code NOT IN (
    			select ar_store_code
    			from pgm_action_responses
    			)
    UNION ALL
    SELECT
    	s.Store_Name,
    	CAST(pss_story_id as INT) as sequenceNr,
    	st_title + ' st_status = ' + CASE WHEN st_status = '1' THEN 'yes' ELSE 'no' END as OutputLine
    From pgm_store_stories as ss
    	inner join pgm_stories as st
    		on ss.pss_story_id = st.st_id
    	inner join stores as s
    		on s.store_code = ss.pss_store_code
    Where pss_story_id > '7829' AND
    	NOT(st_status = '1') AND
    	st_release_date > '2009-11-11' AND -- YYYY-MM-DD format
    	store_code NOT IN (
    			select ar_store_code
    			from pgm_action_responses
    			)
    UNION ALL
    SELECT DISTINCT 
    	s.Store_Name,
    	2147483647 as sequenceNr,
    	'Total = ' + CAST(COUNT(*) as VARCHAR) as OutputLine
    From pgm_store_stories as ss
    	inner join pgm_stories as st
    		on ss.pss_story_id = st.st_id
    	inner join stores as s
    		on s.store_code = ss.pss_store_code
    Where pss_story_id > '7829' AND
    	NOT(st_status = '1') AND
    	st_release_date > '2009-11-11' AND -- YYYY-MM-DD format
    	store_code NOT IN (
    			select ar_store_code
    			from pgm_action_responses
    			)
    GROUP BY s.store_code, s.Store_Name
    Order by 1, 2
    You may be able to optimise the first and last SELECTs in the UNION (not joining with all the tables, getting rid of the DISTINCTs, but for that more info is needed, otherwise you may end up with headers ('Shop n = ') or footers ('Total = ..') without body('to do <st_title 1> actioned = NO')).
    But for some reason, pss_story_id doesn't bring back any ids above 7831, but i know there are some? any ideas guys ?
    Ordering numeric CHAR strings gives results in the line of '2' > '10000000'? You will have to convert those strings back to INT values before you can use ">" with confidence.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Nov 2009
    Posts
    4
    Quote Originally Posted by gvee View Post
    I just see the query "Show the name and the population in descending order of population for each country that has a population of more than 200000000" and what relate to this problem?

  11. #11
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi all,

    I've realised why it didnt bring back the result above that number. its because the Store_code would have been in the database once another to do' job has been completed.

    So, because all stores get a list of 'to do' jobs, eventually they will do it, and because the query searches for store_codes above 'X' for one job, eventually the store will not be found in the database and so wont be returned. Regardless of the job.

    I need to search for all store_codes "not in" a specific table, but based on a specific 'to do' job.

    Hope that all makes sense

    Regards
    MG

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •