| |
| |
| |
|
Page: 1 2 3 4 5 6
Comments:
<0> i think youre not really telling us the real problem
<0> unless its just laziness :)
<1> Carp : I envision it working such that I populate a table with sorted (or in order) possible values of @this temporarily and then figure out some way to order by that table
<2> and by "figure out some way" you will be branching logic
<1> tenfour, so let's say you have 40 columns each having 20 possible values, you want to hand code that?
<2> i.e. CASE WHEN
<0> ok so lets say you wanna create a temp table of ordered keys
<0> how you gonna create that?
<2> VRik sure how else could you possibly do it?
<3> hehehehehheheheheheheh
<3> same problem
<0> heres some serious advice
<0> magic doesnt exist
<1> I have no idea, me and the CTO were talking about how to get around non-dynamic order bys
<1> afk a sec
<2> VRik you haven't expressed any reason not to use CASE WHEN
<2> except "it ****s typing that much"
<0> so you 1) use case, and accept the query is very long 2) rethink your design 3) restrict your design to realistic search options
<0> in my experience, whenever you want to offer 10001 options, the user is only really inetrested in 4 of them
<2> VRik it sounds like you already have it working with CASE WHEN anyway
<2> so you want to invent a whole new strategy, just to get around having to put in a couple more cases?
<0> like i say, here was here before, and we told him for maybe an hour
<2> er damn a flag doesnt sound
<0> and he still eft ***uming we knew nothig and there was some other way
<0> so by that basis, ill contribute no more. good luck my freind !
<3> would dynamic sql be much slower?
<2> heh
<0> and what bit decides what dynamic sql to create ?
<0> why, an IF statement of some sort
<3> just put the parameter in there :)
<2> Carpathia i thought you were ducking out :P
<0> i am
<0> it just irritates me so i had to rejoin :)
<1> No, I should have listened to Carpathia et al the first time, it really just can't be done
<1> I'm just going to use a stored procedure and generate the SQL dynamically can call it a day
<0> :(
<1> I just get stubborn and can't believe this great tool SQL isn't all powerful haha
<0> really though, take a lok at your design again
<0> and ask yourself if this flexibility it really *essential*
<0> if it is, then fair enough
<0> but im a bit dubious
<1> it is unfortunately, so dynamic it must be
<0> ok
<1> dymanic sql then executed I mean
<1> thanks for all the help
<4> i've got a table that is pretty much 2 columns (date (1 row per hour) and count). I'm trying to figure out the best way to get the average of each *day* from the best 9 hours in that day. This is MSSQL any ideas?
<5> how do you define the best 9 hours?
<4> by the count
<4> sorry, should've said "highest hours"
<1> select top 9 datecolumn from mytable orderby coun desc
<5> select top 9 count from table order by count
<5> what VRik said
<4> i can figure out the highest hours, thats easy. I can figure out the average, that is easy too. IT's the combination of both that is puzzling.
<0> that would only bring back the days top 9
<0> he wants an avg of 9 for every day
<1> err woops
<1> sum those, divide by 9
<0> id use an AVG on the subquery personally
<1> or that yeah
<0> but then hed want a group by per day
<5> select top ... (select avg(count), date from mytable group by date) order by ..
<1> I think he wants it to be today only?
<4> no it needs to group by day
<5> if I understand you, that should work
<4> yeah, i think that might do it
<2> does it need to be 9 consecutive hours?
<2> or any 9 hours of the day
<4> any 9
<2> ah yea that's much easier :P
<4> the highest nine
<4> dangit, i have to go
<4> i'll try it out some more, i might be back tomorrow
<4> i hate carpools
<0> select AVG(a.cnt), a.adate from table where a.datetime IN (select top 9 b.cnt, convert(varchar(10), b.datetime) adate from table b where convert(varchar(10), b.datetime) = convert(varchar(10), a.datetime) group by convert(varchar(10), b.datetime) order by count(b.cnt) desc) a
<0> or something
<0> select AVG(a.cnt), a.adate from table where a.datetime IN (select top 9 b.cnt, convert(varchar(10), b.datetime) adate from table b where convert(varchar(10), b.datetime) = convert(varchar(10), a.datetime) group by convert(varchar(10), b.datetime) order by count(b.cnt) desc) a group by convert(varchar(10), a.datetime)
<4> that will be on my clipboard for tomorrow carpathia
<0> its not right, but its not far off ;-)
<4> Yeah, i just need a clue
<4> thanks a lot
<0> time for bed
<0> nite
<0> np
<5> why use the converts in there Carpathia?
<0> to chop of the time portion, so it groups all date with the same day
<0> chop off
<0> or you could group by DAY(date), MONTH(date), YEAR(date)
<5> ah cause hes storing as a datestamp
<5> or whatever its called in mssql
<5> makes sense, good thinking big guy
<0> yeah, a datetime
<0> :-)
<6> on sql2005 it isn't letting me add a index, say that the process has timed out...any clue to how i can add this index?
<1> I bet MSDN would tell you that, msdn.microsoft.com
<7> Hi. I have a table of questions, and another of answers. I want to create a query that returns the question, and the number of answers. I have a query that works mostly, but does not return anything at all for questions with no answers. The query I am using atm is "SELECT Question.QuestionID, Question.Question, COUNT( Answer ) AS replies FROM Answer INNER JOIN Question ON Question.QuestionID = Answers.Question WHERE Answers.Approved
<7> =1 AND Topic=$topic GROUP BY Question;"
<1> try LEFT INNER JOIN instead of INNER JOIN
<7> LEFT INNER JOIN?
<1> hrmm rereading your query
<1> actually a right join since you want the questions that don't have answers I believe
<7> I want the ones with and without answers
<7> is very odd. inner join, left join and right join all return the same results exactly
<7> none of which is what i want!
<1> select Q.QuestionId, Q.Question, COUNT( A.Answer) AS replies FROM Question Q LEFT JOIN ANSWERS ON Q.QuestionId = A.QuestionId WHERE Answers.Approved = true
<1> err ANSWERS A ON heh
<1> Is Approved a bit?
<7> is a byte, can be 0 1 or 2 depending on unapproved, approved or rejected
<1> ok
<1> well my answer is close, cut out the = true and add the rest of your where clause
<7> yes, I am just looking through :)
<7> so essentially use left join and transpose my Q/A in the FROM clause?
<1> select Q.QuestionId, Q.Question, COUNT( A.Answer) AS replies FROM Question Q LEFT JOIN ANSWERS ON Q.QuestionId = A.QuestionId WHERE Answers.Approved = 1 AND Topic=$Topic GROUP BY Question
<1> Ibelieve
<7> I have that, but still the same result. very frustrating
<3> yeah that swrong
<3> because you still ahve answers.approved = 1
<3> answers.approved willb e NULL if an answer doesnt match so you dont get that row in resultset
<3> try
<3> SELECT Q.QuestionId, Q.Question, COUNT(A.AnswerID) AS replies FROM Question Q LEFT JOIN Answers A ON Q.QuestionID = A.QuestionID AND A.Approved = 1 WHERE Topic = :topic GROUP BY Q.Question, Q.QuestionID
<3> how the hell does your query even work
<3> are you using mysql?:PPP
<7> yes, using mysql, but what is wrong with the query? Well spotted re the appoved problem btw, now have a working query
<3> well think for a second :P
<3> if you arent grouping by QuestionID, how can you select it?
<3> there might be several rows with the same question but different QuestionID
<7> true. V. unlikely in the scenario, but technically feasible
<3> thats why its only allowed in weird db's like mysql :P
<7> I could almost use Question.Question as the primary key, but am in the habit of a dcedicated key field
<3> it gets a the field from a random row i guess
<3> random row in the group
<7> I dunno. I am kinda feeling my way with sql after doing a little at colleg a few years back (with access(!!)) and am a little confused over group by and conglomerate fields
<3> nh your db design is fine, just group by the fields you select :P
<7> in my (somewhat wonky) understanding it is ok for me. maybe my wonky understanding is reinforced by mysql's interpretation
<7> so I should group by Question.Question, Question.QuestionID? or would just QuestionID be ok?
<3> both
<7> so if I didnt select questionID it would be ok as is?
<3> yeah
<7> can you explain please? I would like to understand this. If 2 fields are both unique, what is the benefit of grouping by both?
<3> well its just a coincidence that they are unique in this case
<3> they might just as well be not
<3> not be * :P
<7> well, not really conicidence, it is designed that way, but I do understand your point. If non unique fields are grouped there would be a problem for sure. not sure how mysql would deal with that. Maybe an experiment is due.
<7> but I guess you must be correct. it must take the value from a random row :O
<7> ^ experiment done, and your guess was correct. Lesson learnt! Thanks for the help.
<8> MySQL doesn't require that you GROUP BY items not aggregated in the SELECT clause. Yet another reason why MySQL ****s.
<9> jail break
<10> Halo_Four: that makes no sense
<10> if you don't GROUP BY, how can it know what aggregate to apply the aggregate function to?
<8> rmah: Beats the crap out of me.
<8> But it runs
Return to
#sql
or
Go to some related
logs:
#netbsd
+gpupdate +freeze
#politics
#windows
#computers
#sql
#linuxhelp
zolpediem
LIVEPUSSY.EU
#c
|
|