Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 15 of 15
  1. #1
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Sort by subquery?

    Code:
    SELECT TOP 8 h.aId, h.typeId, h.aTitle, h.aDateField, h.aAuthor, h.aIcon, h.blnIcon, h.aUser, (SELECT t.typeName FROM types t WHERE t.typeId = h.typeId), (SELECT TOP 1 f.folderName FROM folders f WHERE f.folderId = (SELECT folderId FROM types WHERE types.typeId = h.typeId)), (SELECT COUNT (*) FROM publishedArticles pA WHERE pA.aId = h.aId) AS paCount, (SELECT COUNT (*) FROM mediaGallery mG WHERE mG.aId = h.aId) AS mediaCount, (SELECT COUNT (*) FROM headers) FROM headers h WHERE h.aId NOT IN (SELECT TOP 8 aId FROM headers ORDER BY h.aTitle ASC ) ORDER BY h.aTitle ASC
    I have that and I want to sort by the subqueries... as you can see I have used aliases for the subqueries using "AS" but when I attempt to sort by it I get an error
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #2
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    Code:
    SELECT TOP 8
    	h.aId,
    	h.typeId,
    	h.aTitle,
    	h.aDateField,
    	h.aAuthor,
    	h.aIcon,
    	h.blnIcon,
    	h.aUser,
    	(SELECT t.typeName FROM types t WHERE t.typeId = h.typeId),
    	(SELECT TOP 1 f.folderName FROM folders f WHERE f.folderId = (SELECT folderId FROM types WHERE types.typeId = h.typeId)),
    	(SELECT COUNT (*) FROM publishedArticles pA WHERE pA.aId = h.aId) AS paCount,
    	(SELECT COUNT (*) FROM mediaGallery mG WHERE mG.aId = h.aId) AS mediaCount,
    	(SELECT COUNT (*) FROM headers)
    FROM headers h
    WHERE h.aId NOT IN (SELECT TOP 8 aId FROM headers ORDER BY h.aTitle ASC )
    ORDER BY h.aTitle ASC
    3 subqueries do not have an alias and one uses = where it should use in.. however i would just use the subquery again in the order by. i will be interested to hear a solution to this as i have encountered the prblem of not being able to order by aliases a few times. the subquery in the order by clause is a hog but with the amount of subqueries you have in thre anyway i doubt it will make much difference its gunna be a hog anyway

  • #3
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
    SELECT TOP 8
    	h.aId,
    	h.typeId,
    	h.aTitle,
    	h.aDateField,
    	h.aAuthor,
    	h.aIcon,
    	h.blnIcon,
    	h.aUser,
    	(SELECT t.typeName FROM types t WHERE t.typeId = h.typeId),
    	(SELECT TOP 1 f.folderName FROM folders f WHERE f.folderId = (SELECT folderId FROM types WHERE types.typeId = h.typeId)),
    	(SELECT COUNT (*) FROM publishedArticles pA WHERE pA.aId = h.aId) AS paCount,
    	(SELECT COUNT (*) FROM mediaGallery mG WHERE mG.aId = h.aId) AS mediaCount,
    	(SELECT COUNT (*) FROM headers)
    FROM headers h
    WHERE h.aId NOT IN (SELECT TOP 8 aId FROM headers ORDER BY h.aTitle ASC )
    ORDER BY h.aTitle ASC
    You can not sort a subquery.

  • #4
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    im prety sure you can then to get the correct top 8 results.. you cannot sort when its group by or only contains agregate functions such as count.. i think.. im not sure.. i thought he meant outside of that anyway

    i thought he meant this sorta thing:
    Code:
    ...
    FROM headers h
    WHERE h.aId NOT IN (SELECT TOP 8 aId FROM headers ORDER BY h.aTitle ASC )
    ORDER BY h.aTitle, mediaCount
    where i suggested
    Code:
    ...
    FROM headers h
    WHERE h.aId NOT IN (SELECT TOP 8 aId FROM headers ORDER BY h.aTitle ASC )
    ORDER BY h.aTitle, (SELECT COUNT (*) FROM mediaGallery mG WHERE mG.aId = h.aId)
    Last edited by ghell; 08-26-2005 at 08:15 PM.

  • #5
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Yah , Ghell's unto my idea..
    I just thought that it was necessary to pass the same order by line to the "NOT IN" subquery so that it will exclude the correct records.

    I'm designing a control that dislpays articles... so i'm tryin to include the functionality of sorting by different fields... and its many differen fields so.... yah
    Gonna try sorting by the sub-query expression and will get back to you!
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #6
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Sorting by the expression didn't work.. got an error about the expression.

    Funny though... I get this error when I attempt to use aliases..
    Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
    [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.


    And I ususally get that error when I misspell a field or summin....
    How am I gonna resolve this one? I need to sort by the subquery...
    Need to find a way aronud...
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #7
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    One way would be to do 2 sql calls. The first would be to grab the ids that are in the sub query:
    Code:
    SELECT TOP 8 aId FROM headers ORDER BY aTitle ASC
    Then loop through and a make a comma delimited list to pass back into the second query. Your other option would be to create a stored procedure if you wanted to do it all in one call.

  • #8
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by neocool00
    One way would be to do 2 sql calls. The first would be to grab the ids that are in the sub query:
    Code:
    SELECT TOP 8 aId FROM headers ORDER BY aTitle ASC
    Then loop through and a make a comma delimited list to pass back into the second query. Your other option would be to create a stored procedure if you wanted to do it all in one call.
    Yea... sounds interesting but uh... when working even with delimited expressions... i've found a slight prob...
    the id 1 is found in 11,15,18 and like wise 15 can be found in 115,515,615... If u get wot I'm saying... How do you suggest I delimit them if I tried that route
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #9
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    @jay,
    So id is not an integer? If it's an integer, no problem. You just pass in 1,2,3,4.... It's not a "LIKE" statement, so it does a straight comparison. Therefore 1=1 not 1 LIKE 10.

  • #10
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by neocool00
    @jay,
    So id is not an integer? If it's an integer, no problem. You just pass in 1,2,3,4.... It's not a "LIKE" statement, so it does a straight comparison. Therefore 1=1 not 1 LIKE 10.
    Not sure I understand. What function are you suggesting I use in the sql statement, after I pass in the delimited id's?
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #11
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    @jay,
    Write your code to execute the subquery sql statement as a stand alone. Loop through the records storing them in a comma delimited variable. Then pass that variable into your next sql statement.

    Code:
    Set rs = Connection.Execute("SELECT TOP 8 aId FROM headers ORDER BY aTitle ASC")
    Do While NOT rs.EOF
    If strIds <> "" Then strIds = strIds & ","
    strIds = strIds & rs("aId")
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Connection.Execute("...WHERE h.aId NOT IN (" & strIds & ")...")

  • #12
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by neocool00
    @jay,
    Write your code to execute the subquery sql statement as a stand alone. Loop through the records storing them in a comma delimited variable. Then pass that variable into your next sql statement.

    Code:
    Set rs = Connection.Execute("SELECT TOP 8 aId FROM headers ORDER BY aTitle ASC")
    Do While NOT rs.EOF
    If strIds <> "" Then strIds = strIds & ","
    strIds = strIds & rs("aId")
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Connection.Execute("...WHERE h.aId NOT IN (" & strIds & ")...")
    Alright... thats clear. But I'm trying to remember. What was the reason for this initial suggestion? I don't quite recall... how would this method assist in the sorting?
    Oh, by the way.. don't know if I mentioned this but I did some checkin around and I confirmed that the NOT IN section of the query was running properly.
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #13
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The reason was because you cannot sort a subquery. I don't see how your subquery was working unless you removed the sort by clause. Please post what you got to work.

  • #14
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by neocool00
    The reason was because you cannot sort a subquery. I don't see how your subquery was working unless you removed the sort by clause. Please post what you got to work.
    Remember what I'm doing is adding functionality to sort by different fields. All the fields that come from tables have been straightforward. But when it came to sorting by the ones that are subquery fields... thats when I posted.
    Last edited by jaywhy13; 08-30-2005 at 08:46 PM. Reason: Forget the part about expressions
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #15
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Any head way on this problem anyone?
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished


  •  

    Posting Permissions

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