...

View Full Version : Sort by subquery?



jaywhy13
08-26-2005, 03:18 PM
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

ghell
08-26-2005, 04:40 PM
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 ASC3 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 :rolleyes:

neocool00
08-26-2005, 06:29 PM
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.

ghell
08-26-2005, 07:09 PM
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:

...
FROM headers h
WHERE h.aId NOT IN (SELECT TOP 8 aId FROM headers ORDER BY h.aTitle ASC )
ORDER BY h.aTitle, mediaCountwhere i suggested
...
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)

jaywhy13
08-26-2005, 09:09 PM
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 :o
Gonna try sorting by the sub-query expression and will get back to you! :thumbsup:

jaywhy13
08-26-2005, 09:14 PM
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... :o
Need to find a way aronud... :D

neocool00
08-29-2005, 01:13 PM
One way would be to do 2 sql calls. The first would be to grab the ids that are in the sub query:

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.

jaywhy13
08-29-2005, 04:42 PM
One way would be to do 2 sql calls. The first would be to grab the ids that are in the sub query:

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

neocool00
08-29-2005, 06:34 PM
@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.

jaywhy13
08-30-2005, 03:30 AM
@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?

neocool00
08-30-2005, 06:32 AM
@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.



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 & ")...")

jaywhy13
08-30-2005, 02:43 PM
@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.



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.

neocool00
08-30-2005, 04:42 PM
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.

jaywhy13
08-30-2005, 06:49 PM
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.

jaywhy13
09-06-2005, 04:41 PM
Any head way on this problem anyone?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum