| |
| |
| |
|
Page: 1 2 3 4 5 6
Comments:
<0> which is fine (all done) except for if two contacts have the same importance value
<1> then you're not choosing the most important one
<1> seems like a rather simple query
<0> oh kay
<0> when you say I'm not choosing the most important one. can you elaborate?
<0> ps - did I say the dbms was access?
<1> select max(importanceID) as bigShotValue, contactName, dealerName from contacts inner join dealers on contact_DealerID = dealer_ID group by contactName, dealerName order by dealerName, contactName
<1> sorry, let me restate
<0> df, thats pretty much what I have (except for the importance value is min) but, if there are two contacts with the same importance value then they are both returned.
<1> because you're grouping incorrectly
<1> which is what i just did
<0> ahh
<1> you're trying to return too many cols in the query
<0> I have a sub query which gives the dlr_id and the importance_value
<1> anyway, the point is to only get the id of the most important contact per dealer and use that is the IN condition for the 'full' query
<1> similar to what you have
<0> which gives a unique list of dlr_id's and the importance
<0> hmm
<1> you wanted the contact id though
<0> yeah, but I figured that as the contact has to be unique to the dlr, I would start by finding the min(importance_value) per dealer
<0> . o (min(importance_value)) looks so wrong here)
<0> the problem comes when I try and link that subquery to the contacts table, as in a handfull of cases (<400~) there are two contacts with the same importance
<0> think I may have stumbled blindly into a limitation of my db schema
<1> MattyT, if you max() or min() something, that, by definition, should yield only a single result
<1> therefore, its obvious that you are grouping by too many columns
<1> because then its the 'minimum of these two cols', not the 'minimum of the one col i really want'
<0> indeed, but at the moment, I am displaying two fields in a subquery and two fields in the parent
<1> so, get rid of the one in the subquery that is ****ing you up
<0> the subquery is working perfikly
<1> apparently not, if its returning two values per dealer
<0> I get a list of dlr_id's and a list of the min(importance values)
<0> , however, now I know which level of importance to look for - I have the problem when there are two contacts with the same importance
<0> ...
<1> then add an additional order by col to then sort alphabetically
<1> someone is most important
<0> the only way to solve this is to group by dlr_id and min(importance) and SHOW(contact_id) in the subquery, but as we all know - this is ENTIRELY impossible
<1> i have no idea what you just said
<1> so, i take your word for it
<0> ok-no worries, let me start again!
<0> ok fair enough!
<2> why dont you, instead of using MIN and group BY
<0> df - sorted. I created an extra field per contact. then ran a query which generated a "importance_per_dealer" number.(created by importance/alphabetically on name) and then selected 1 from this field. (which is now unique)
<2> use select TOP 1 order by importance DESC, someotherfield DESC
<0> Carpathia: thanks - but its sorted
<2> hm that sounds like a big bodge, but ok
<0> top 1 would only return the top first record.
<0> Carpathia: yeah - but it only needed to be run once
<2> ok
<3> mornin
<0> awwww crap. after all that - it apparently "isnt what was required"
<0> there is no way I can generate what is required with sql. onto vbscript then... woot</sarcasm>
<3> whatya trying to do matty?
<0> SicLuDe: its a list of who we should contact in an emergency
<0> (by email - D'oh)
<0> we have a list of contacts per dealer, some are names with no email address, some are general email addresses.
<0> I have to create a list of the "most important" name, and then the first available email address after that.
<0> gonna use vbscript for it - tis doing ma head in trying to find some botch to do it in sql.
<0> * some names do have email address (in case that wasnt clear)
<2> use a CASE
<4> if you need to pick the first non-null value from a sequence of values, and you're using MS SQL Server, use COALESCE
<4> I'm sure other databases have the same function
<0> will look at that, thanks LVK
<5> leaving on a trip tommorrow... 5 out of 6 corp amex came today for my work... mine had to be the one missing. :P
<6> whats more important when using EXPLAIN to optimize queries: searching the fewest number of rows, or avoiding temp/filesort?
<2> temp is the worst, by far
<6> so 25000 row where/index is better than 250 row temp/filesort?
<7> Depends. Try it both ways.
<2> i dont know, therell be a threshold where its better to temp file than to search loads
<2> but generally temp file is the biggest perf killer on mysql
<2> but the most important thing on mysql, is to make sure youre not just using any old config file
<2> the config is crucial
<6> what makes the config so crucial?
<2> because its decides how mysql will operate
<2> depending on ram, and the config, it may be creating temp tables when it could do it all in ram
<2> but the config tells it not to
<7> If query optimization was as simple as "do this this and this, always" any trained chimp could do it.
<7> Its not, so they haev a hard time with it.
<2> yup, even gurus simply have to run benchmarks and compare
<8> So what would be the best method to create a search feature use LIKE with my SELECTs?
<8> :-)
<9> Write a scalar function
<6> wtf
<6> djfuji?
<6> youre not the same one i was talking to on aim a few hours ago are you?
<8> thanks Halo
<0> god damn I hate vb
<7> Use soundex! :p
<6> thats ridiculous, theres more than one djfuji
<8> Halo_Four: that's a SQL Scalar Function
<8> ?
<9> yes
<8> thanks
<0> Drk`Angel: was that directed towards me?
<0> I think anything could be better than vba
<0> at least vb has a "save before run" option
<9> CREATE FUNCTION dbo.MyLike (@input VARCHAR(50)) RETURNS BIT AS BEGIN IF @input = 'Poop' RETURN 1; RETURN 0; END
<9> SELECT * FROM MyTable WHERE dbo.MyLike(Field1) = 1
<8> so like SP
<6> when using indices with left join, where, and gruop, what order does the index have to be in? left join, group, where? group, left join, where? group, where, left join?
<0> so - I was working in vba on a script for ages. (***uming that vba would share my "save before running" options).... but NOOOOOOOOOOOOO
<0> all work lost
<0> woot
<8> ok I see thanks bro
<7> Halo: That's about as useful as soundex, too ;)
<8> But doesnt a scalar function return a single value?
<7> It depends on what you're searching and how. you might want to write a scalar (hash) function. You might use like. You might use fulltext indexing. You mgiht use some combination.
<7> There is no "best way"
<7> So, stop asking for one.
<8> its a earch for a ASP.NET Page
<8> or site
<7> Jack google's code and use that.
<6> my brother got offered a job at google
<6> but i guess thats what you get when you go to school at caltech
<6> and when you used to work for yahoo
<9> Calcutta Technology Institute?
<6> california institute of technology, the MIT of the west
<10> caltech ****s :P
<10> NJIT > Caltech :D
<7> Nice insult to MIT there. ;)
<10> i know :)
<6> you guys dont even know wtf youre talking about
<7> Was in the MIT bookstore the other day looking for something, and they had a sweatshirt that said in giant letters across it "HARVARD" and then under it in smaller letters "Because not everyone can go to MIT"
<10> HAHAHHAHAH
<10> Drk`Angel: all together now o/~ What do you do with a BA in english o/~
<10> o/~ four years of college and plenty of knowledge, has earned me this useless degreeeee o/~
<10> anyway :)
<7> heh
<7> Friend of mine graduated last year with a ba in english.
<7> He's now applying to law school.
<7> heh
<10> he got the point
<10> hey guys, i'm using MSSQL and i got a little issue with date formats. i looked on msdn, on the t-sql manual and i found out the SET DATEFORMAT decleration?function? (dunno which of the two it is). So i tried to do a little example in the admin studio
<7> "****. Useless degree!"
<7> Format dates on the client side.
<7> That's a display issue, don't worry about making the db do it.
<10> i did
<10> *sigh*
<10> here's the whole issue
<10> i'm using .NET, right? and my program works great on my database
<10> but this ****ing luser got his dates in a whole other format
<7> Use real parameters. Set the data type to DateTime. Don't try to inline in formatted strings. ;)
<9> Why would X have to do with Y?
<10> Halo_Four: nothing.
<10> i'm just giving out as much information as i can :P
<10> so, the luser gets an exception when selecting the date field in his database
<7> Basically: The database and client date format have nothing to do with each other.
<7> If you're having problems, you need to rewrite some code and decouple.
<10> meaning?
<10> (the decouple part)
Return to
#sql
or
Go to some related
logs:
#beginner
#gentoo
#worldcup
#computers
stuck on u quotes
#politics
#visualbasic
linux lirc_hauppauge
cheapest CPU intel
#opengl
|
|