SQL Question
in [Visual Basic]
|
Prev: Project Explrer
Next: VB Editor in Excel
From: David Kaye on 31 Mar 2010 05:05 Hello folks, I'm reposting this here because I haven't gotten response elsewhere and I know that people here are Jet SQL savvy: sfdavidkaye2(a)yahoo.com (David Kaye) wrote: >Here's the deal. I'm trying to select only the records with the largest count >of a particular condition. It's a list of recording artists and genres. It >looks like this: > >select count(genre),genre,artist from songs group by genre,artist order by >artist,count(genre) desc,genre > >This gives me a list by artist of the genres associated with this artist, >organized by the most common genre applied to that artist. For instance, one >artist looks like this: > >7 Rap Andre Nickatina >4 Other Andre Nickatina >2 Hiphop Andre Nickatina >2 Gangsta Andre Nickatina > >Okay, so far so good. But what I want is to get a recordset showing ONLY the >first record. Please, if you respond, don't tell me something theoretical, >but something that actually works. For instance, "top 1" won't work. It will >only bring up one artist. > >
From: Jeff Johnson on 31 Mar 2010 15:49 "David Kaye" <sfdavidkaye2(a)yahoo.com> wrote in message news:houvo6$tmq$2(a)news.eternal-september.org... >>Here's the deal. I'm trying to select only the records with the largest >>count >>of a particular condition. It's a list of recording artists and genres. >>It >>looks like this: >> >>select count(genre),genre,artist from songs group by genre,artist order by >>artist,count(genre) desc,genre >> >>This gives me a list by artist of the genres associated with this artist, >>organized by the most common genre applied to that artist. For instance, >>one >>artist looks like this: >> >>7 Rap Andre Nickatina >>4 Other Andre Nickatina >>2 Hiphop Andre Nickatina >>2 Gangsta Andre Nickatina >> >>Okay, so far so good. But what I want is to get a recordset showing ONLY >>the >>first record. Please, if you respond, don't tell me something >>theoretical, >>but something that actually works. For instance, "top 1" won't work. It >>will >>only bring up one artist. > I'm reposting this here because I haven't gotten response elsewhere and I > know > that people here are Jet SQL savvy: Totally off the top of my head: SELECT COUNT(*), Genre, Artist FROM songs GROUP BY Genre, Artist HAVING COUNT(*) = (SELECT MAX(GenreCount) FROM (SELECT COUNT(*) AS GenreCount, Genre, Artist FROM songs GROUP BY Genre, Artist)) Hey, whaddya know? I tried it and it worked.
From: David Kaye on 31 Mar 2010 20:05 "Jeff Johnson" <i.get(a)enough.spam> wrote: >SELECT COUNT(*), Genre, Artist >FROM songs >GROUP BY Genre, Artist >HAVING COUNT(*) = (SELECT MAX(GenreCount) FROM (SELECT COUNT(*) AS >GenreCount, Genre, Artist FROM songs GROUP BY Genre, Artist)) > >Hey, whaddya know? I tried it and it worked. Nope, it doesn't work. It brings up only 1 record, the artist with the highest count of the same genre. What I need is *every* artist and the highest count of the same genre for that artist. It'll use an embedded statement of some sort, I know that much, but I can't quite wrap my brain around it.
From: Jeff Johnson on 1 Apr 2010 09:57 "David Kaye" <sfdavidkaye2(a)yahoo.com> wrote in message news:hp0kfi$pll$3(a)news.eternal-september.org... >>SELECT COUNT(*), Genre, Artist >>FROM songs >>GROUP BY Genre, Artist >>HAVING COUNT(*) = (SELECT MAX(GenreCount) FROM (SELECT COUNT(*) AS >>GenreCount, Genre, Artist FROM songs GROUP BY Genre, Artist)) >> >>Hey, whaddya know? I tried it and it worked. > > Nope, it doesn't work. It brings up only 1 record, the artist with the > highest count of the same genre. > > What I need is *every* artist and the highest count of the same genre for > that > artist. > > It'll use an embedded statement of some sort, I know that much, but I > can't > quite wrap my brain around it. Enjoy: SELECT GCount, Genre, main.Artist FROM (SELECT Artist, Genre, COUNT(*) AS GCount FROM songs GROUP BY Artist, Genre) AS main INNER JOIN (SELECT Artist, Max(GCount2) AS MaxGenre FROM (SELECT Artist, Genre, COUNT(*) AS GCount2 FROM songs GROUP BY Artist, Genre) AS sub2 GROUP BY Artist) AS sub1 ON sub1.Artist = main.Artist AND main.GCount = sub1.MaxGenre This sort of thing is one of the most complex things you can do in SQL since it goes against the set theory that is the very heart of SQL. I recommend you break it apart into its component pieces to see what's actually happening. I actually recommend doing this sort of row-based filtering thing in code instead of convoluted SQL.
From: David Kaye on 1 Apr 2010 15:49
"Jeff Johnson" <i.get(a)enough.spam> wrote: >Enjoy: > >SELECT GCount, Genre, main.Artist >FROM (SELECT Artist, Genre, COUNT(*) AS GCount FROM songs GROUP BY Artist, >Genre) AS main INNER JOIN (SELECT Artist, Max(GCount2) AS MaxGenre FROM >(SELECT Artist, Genre, COUNT(*) AS GCount2 FROM songs GROUP BY Artist, >Genre) AS sub2 GROUP BY Artist) AS sub1 ON sub1.Artist = main.Artist AND >main.GCount = sub1.MaxGenre Thank you thank you thank you!!! This does EXACTLY what I'm trying to do. >This sort of thing is one of the most complex things you can do in SQL since >it goes against the set theory that is the very heart of SQL. I recommend >you break it apart into its component pieces to see what's actually >happening. I actually recommend doing this sort of row-based filtering thing >in code instead of convoluted SQL. But what's amazing is that I just did it against 38,000 records (entries in the songs table) using DAO 3.6 and the recordset came back in 2.2 seconds! So even though it's convoluted, it's not beyond the abilities of Jet. I can't tell you how pleased I am with your help. Again, thank you very much. |