Count-function and display top value in related ta

Results 1 to 2 of 2

Thread: Count-function and display top value in related ta

  1. #1
    Join Date
    Dec 1969

    Default Count-function and display top value in related ta

    I have a MainTable which is related to Pic by PicID. The table Pic may contain pictures related to each post in MainTable. When I do a select on MainTable I want to add to columns that displays how many posts that are related in Pic and the "top-picture" selected by a priority column.<BR><BR>I have tried, but these SQLs don&#039;t give a good result. When I run "ViewOneMainTable" it is generated duplicates by the posts containing related pictures in Pic:<BR><BR>"ViewOneMainTable"<BR>SELECT dbo.MainTable.*, dbo.Pic_Count.CountPic AS CountPic, dbo.Picture_GetFirstFilename.Filename AS Filename FROM dbo.MainTable LEFT OUTER JOIN Picture_GetFirstFilename ON dbo.MainTable.RentID = dbo.Picture_GetFirstFilename.RentID LEFT OUTER JOIN dbo.Pic_Count ON dbo.MainTable.RentID = dbo.Pic_Count.RentID<BR><BR>"Pic_Count"<BR>SELECT COUNT(PicID) AS CountPic, RentID FROM dbo.Pic GROUP BY RentID<BR><BR>"Picture_GetFirstFilename"<BR>SELECT TOP 100 PERCENT RentID, PicID, Filename FROM dbo.Pic ORDER BY Priority

  2. #2
    Join Date
    Dec 1969

    Default RE: Count-function and display top value in relate

    SELECT M.*, (SELECT COUNT(*) FROM Pics P WHERE P.PicID = M.PicID) as CountOfPics, (SELECT TOP 1 PicName FROM Pics WHERE P.PicID = M.PicID ORDER BY Priority DESC) as TopPic<BR>FROM MainTable<BR><BR>something like this, if you need a more specific answer, post the table layout.

Posting Permissions

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