select high value in a join

Results 1 to 2 of 2

Thread: select high value in a join

  1. #1
    Join Date
    Dec 1969

    Default select high value in a join

    Hi all<BR><BR>I’m trying to create a view that will just grab the highest value of the joined table. There will be a lot of data therefore i want to do all queries database side<BR><BR>Let me explain simply<BR><BR>Table 1<BR>Band Id<BR>1 <BR>2<BR>3<BR><BR>Table 2<BR>CD - BandId – Rating<BR>500 - 1 - 5<BR>501 - 1 - 8<BR>502 - 2 - 5<BR>503 - 2 - 7<BR>504 - 3 - 9<BR>505 - 3- 5<BR><BR>Ok, so what I want to do is create a join and just grab the CD of each band that has the highest rating. Therefore would look like this<BR><BR>JoinedTable<BR>CD - BandId – Rating<BR>501 - 1 - 8<BR>503 - 2 - 7<BR>504 - 3 - 9<BR><BR>Much thanks<BR><BR>Anthony<BR>

  2. #2
    Join Date
    Dec 1969

    Default Not hard...

    ...a couple of ways depending on what DB you are using.<BR><BR>The "base" query is this:<BR> SELECT BandID, Max(Rating) <BR> FROM table2<BR> GROUP BY BandID<BR><BR>That would get you<BR> 1 - 8<BR> 2 - 7<BR> 3 - 9<BR><BR>But you can NOT get the CD value by doing that, since it&#039;s different for each rating. Right?<BR><BR>So you have to JOIN *that* query *BACK* to Table2 again.<BR><BR>And whether you can do that "inline" or not depends on what DB you are using. So...What DB *are* you using?<BR><BR>Oh...and one more question: What about ties? Suppose that you had<BR> Table 2<BR> CD - BandId – Rating<BR> 500 - 1 - 8<BR> 501 - 1 - 8<BR><BR>What do you want to do in this case?<BR><BR>Which CD should be chosen in case of ties?<BR> <BR>

Posting Permissions

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