Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recent content by graefalexander

  1. graefalexander

    COUNT() without GROUPing / Optimization

    No its not, youre right. However, its not very important, because i could simply write "0" :-). Albums which did fully match dont have a special ranking... i simply changed the count of matched tracks to the total of matched tracks, thats why it now reads Albums.TrackCount-Albums.TrackCount...
  2. graefalexander

    COUNT() without GROUPing / Optimization

    I'n my dreams, I had the solution: SELECT Albums.Artist, Albums.Title, Tracks.Artist, Tracks.Title, Tracks.Extended, Discs.Ordinal, Tracks.Ordinal, Albums.TrackCount, Albums.TrackCount AS TracksMatched, ((Albums.TrackCount-Albums.TrackCount)*100)/(Albums.TrackCount) AS Rank FROM...
  3. graefalexander

    COUNT() without GROUPing / Optimization

    Hello, I did some tweaking on the database itself, and now get about 500msec for my query. However I tried to do it in steps, to see where much time goes in. These are the actual queries, which correspond to the sample queries above: SELECT Albums.Artist, Albums.Title, Albums.TrackCount...
  4. graefalexander

    COUNT() without GROUPing / Optimization

    I'm sorry, you're right. I didnt understand the query fully. Adding the WHERE-clause would yield the following: SELECT * FROM ( People INNER JOIN (SELECT LocationID, COUNT(*) AS LocationCount FROM People WHERE People.Name LIKE...
  5. graefalexander

    COUNT() without GROUPing / Optimization

    After condensing your query, it seems there is only one additional join to Location wihtout any advantage. If there will be any effect on performance, it wont be positive. And the old problem remains, the WHERE-clause needs to be inserted double, if the result should only include and count for...
  6. graefalexander

    COUNT() without GROUPing / Optimization

    No Effect, the alias "AS Location" could be any other name. No, not! All quoted queries are tested against a real database, and all values I used were in the database this way, only to be sure the result can be reproduced. The real world application is a bit more complicated, too complicated...
  7. graefalexander

    COUNT() without GROUPing / Optimization

    Hello, I do have two tables: CREATE TABLE People ( PeopleID INTEGER PRIMARY KEY, LocationID INTEGER, Name VARCHAR ); PeopleID LocationID Name --------------------------------------- 1 1 Andreas 2 1...
See more

Part and Inventory Search

Back
Top