26. februar 2005 - 11:15Der er
6 kommentarer og 1 løsning
Order by i SQL med Select DISTINCT
Hej,
Jeg har en SQL select med en distinct udvælgelse. - Se nedenstående SQL-kode.
Nu ønsker jeg at lave en sortering på feltet "ManufacturerID" i tabellen tbl_Reviews.
Når jeg prøver det får jeg denne fejl: Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC Microsoft Access Driver] ORDER BY clause (ManufacturerID) conflicts with DISTINCT.
---SQL--- ReviewSQL2 = "Select DISTINCT(ModelId), " &_ "(select AVG(Rating) FROM tbl_Reviews r9 WHERE r9.ModelID = tbl_Reviews.ModelID and ReviewActive=1) AS RRating, " &_ "(select DISTINCT TOP 1 ReviewDate FROM tbl_Reviews r9 WHERE r9.ModelID = tbl_Reviews.ModelID order by ReviewDate desc) AS TopReview," &_ "(select COUNT(ModelID) FROM tbl_Reviews r9 WHERE r9.ModelID = tbl_Reviews.ModelID and ReviewActive=1) AS CountModelID," &_ "(select Model from tbl_RModels where tbl_RModels.ModelID = tbl_Reviews.ModelID)as RModel, " &_ "(select Category from tbl_RCategories where tbl_RCategories.CatID = tbl_Reviews.CatID order by Category asc)as RCategory, " &_ "(select ManufacturerID from tbl_RManufacturers where tbl_RManufacturers.ManufacturerID = tbl_Reviews.ManufacturerID order by ManufacturerID asc)as RManID, " &_ "(select Manufacturer from tbl_RManufacturers where tbl_RManufacturers.ManufacturerID = tbl_Reviews.ManufacturerID)as RManufacturer " &_ "from tbl_Reviews where ReviewActive=1 and CatID="& request.QueryString("CatID") &"" set rsReviewSQL2 = conn.execute(ReviewSQL2) ---SQL---
Jeg håber at I kan hjælpe mig videre. God weekend.
Nej ikke med Access den kan ikke sortere efter et alias navn.
Har du overvejet at lave det som en SQL sætning med joins så kan du sortere efter funktionen igen. Eks. "select avg(kol), kol as gennemsnit from tbl group by kol order by avg(kol)
Hvis du laved et med joins vil din SQL sætning blive sådan her:
SQL = "SELECT tbl_Reviews.ModelId, tbl_Reviews.ManufacturerID as RManID, Avg(tbl_Reviews.Rating) AS RRating, " SQL = SQL & "Max(ReviewDate) as TopReview, Count(tbl_Reviews.ModelId) AS CountModelID, tbl_RModels.Model as RModel, tbl_RCategories.Category AS RCategory, " SQL = SQL & "tbl_RManufacturers.Manufacturer AS RManufacturer " SQL = SQL & "FROM ((tbl_Reviews INNER JOIN tbl_RModels ON tbl_Reviews.ModelId = tbl_RModels.ModelID) " SQL = SQL & "INNER JOIN tbl_RManufacturers ON tbl_Reviews.ManufacturerID = tbl_RManufacturers.ManufacturerID) " SQL = SQL & "INNER JOIN tbl_RCategories ON tbl_Reviews.CatID = tbl_RCategories.CatID " SQL = SQL & "WHERE tbl_Reviews.ReviewActive=1 AND tbl_Reviews.CatID= " & Request.QueryString("catid") SQL = SQL & " GROUP BY tbl_Reviews.ModelId, tbl_Reviews.ManufacturerID, tbl_Reviews.ReviewActive, tbl_RModels.Model, tbl_RCategories.Category, tbl_RManufacturers.Manufacturer " SQL = SQL & "ORDER BY tbl_Reviews.ManufacturerID"
Så hvis du også vil sorter efter gennemsnittet kan det tilføjes sådan her:
SQL = "SELECT tbl_Reviews.ModelId, tbl_Reviews.ManufacturerID as RManID, Avg(tbl_Reviews.Rating) AS RRating, " SQL = SQL & "Max(ReviewDate) as TopReview, Count(tbl_Reviews.ModelId) AS CountModelID, tbl_RModels.Model as RModel, tbl_RCategories.Category AS RCategory, " SQL = SQL & "tbl_RManufacturers.Manufacturer AS RManufacturer " SQL = SQL & "FROM ((tbl_Reviews INNER JOIN tbl_RModels ON tbl_Reviews.ModelId = tbl_RModels.ModelID) " SQL = SQL & "INNER JOIN tbl_RManufacturers ON tbl_Reviews.ManufacturerID = tbl_RManufacturers.ManufacturerID) " SQL = SQL & "INNER JOIN tbl_RCategories ON tbl_Reviews.CatID = tbl_RCategories.CatID " SQL = SQL & "WHERE tbl_Reviews.ReviewActive=1 AND tbl_Reviews.CatID= " & Request.QueryString("catid") SQL = SQL & " GROUP BY tbl_Reviews.ModelId, tbl_Reviews.ManufacturerID, tbl_Reviews.ReviewActive, tbl_RModels.Model, tbl_RCategories.Category, tbl_RManufacturers.Manufacturer " SQL = SQL & "ORDER BY tbl_Reviews.ManufacturerID, Avg(tbl_Reviews.Rating)"
Mht. MSSQL kender jeg ikke nok til databasen så det kan jeg ikke svare på.
MySQL kan sorter på et alias.
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
