User Profile

bbaaking · Apr 26 '12, 03:02 AM

the further discuss has moved to:http://bytes.com/topic/mysql/answers...-a#post3716261

thanks for Rabbit's help.

bbaaking · Apr 26 '12, 02:52 AM

hi Rabbit,
I googled "having" usage, and I tried this statement:
select TCID,ID v1, endTime from service group by TCID having v1>20
It is right. So there must be something wrong with "case when".
But how to fix it. I have no idea. Can you give me a further hint?

bbaaking · Apr 26 '12, 01:36 AM

sorry, I had made a big mistaken for I missed your code. I must be a fool. please forgive me.

I have changed the convert function, and met a error message: "Unknown column 'T2.EndMonth' in 'having clause'", It is just like what u have said.

I　will study your statement carefully, for It is too complicated to me to understand, then I will make more fix.

thank you.

bbaaking · Apr 25 '12, 09:14 AM

hi Rabbit
As much as I know, mysql does not support these keywords:with,o ver,row_number, partition by.
I think It must be a hard job to make this conversion, and out of my ablility, Maybe out of the most people's ability.
Anyway, thanks for your suggestion.

Actually, I'm studying ESPROC as It is my only practical solution. I will take It as my final solution in the project at this friday if I can't make...

bbaaking · Apr 25 '12, 12:57 AM

hi Luuk,
I need a MySQL solution.
I have got a MSSQL solution from a friend, but I(and this friend) can't onvert it to MySQL, for they are too different, and It is out of my ability.
check the MSSQL solution:

Code:

WITH MonthCount AS (SELECT   DATEDIFF(mm, MIN(EndTime), MAX(EndTime)) + 1 AS MonthCountField
                                    FROM      service),
rank_of_person as(
		select

...

bbaaking · Apr 24 '12, 08:49 AM

hi Rabbit,
thanks for your reply.
but your suggestion is out of my ability, I can't do it correctly. as you know, there seems no "with","over"," partition by" in mysql syntax. I can't write it out. so I need a help here.

bbaaking · Apr 24 '12, 07:13 AM

after filtering duplicate date, the TCID and cnt(Personal Total Satisfied Services this Month) and rank should be:

Code:

    month    cnt    TCID    rank
    1    4    Andrew    1
    1    1    Daniel    5
    1    2    Dyla    3
    1    3    Jacob    2
    1    2    Tyler    3
    2    4    Andrew    1
    2    1    Daniel    5
    2    4    Dyla    1
    2    4    Jacob    1
    2    2    Tyler

...

bbaaking · Apr 24 '12, 07:03 AM

hi Rabbit,
Thank you for your reply. Maybe I didn't give the clear information, I'm sorry for that.

I have run that SQL using EMS SQL Manger just now and few days befor, It can run correctly without any syntax wrong. I have taken a screenshot as below:

Let me explain:
1. this statement was not written out by myself. It was from another net friends.
2. Actually, I do not understand this...

bbaaking · Apr 24 '12, 02:56 AM

Finally,I find a solution(http://stackoverflow.com/questions/1...75434#10275434).
that is using esProc to solve this problem. esProc is the only solution I found. Yes, It can return a resultSet object and invoked by report Tool ( I'm using Jasper). But I still need a SQL solution. so, please told me If there are any SQL solution. Thanks for your reply....

bbaaking · Apr 24 '12, 02:54 AM

hi luuk,
thanks for your work though you didn't solve this problem. You are a nice guy.
I must use SQL because I should return a dataset/resultSet to reporting tools , Jasper only identify SQL ( JAVA bean may does too, but It seems too complicated).
I find a solution(http://stackoverflow.com/questions/1...75434#10275434).
that...

bbaaking · Apr 23 '12, 05:55 AM

Thank you, luuk.
I noticed that your statement is almost right,but there must be some mistaken, because I want rank but not rownumber by order.
as an example: 1,1,10 rank is 1,1,3. otherwise, I cann't chose the people with the same data.

after filtering duplicate date, the TCID and cnt(Personal Total Satisfied Services this Month) and rank should be:

Code:

month	cnt	TCID	rank
1	4	Andrew	1
1	1	Daniel	5
1

...

bbaaking · Apr 19 '12, 02:57 AM

many thanks for Rabbit

bbaaking · Apr 19 '12, 02:47 AM

thank you Rabbit, I will do as what u have said

bbaaking · Apr 18 '12, 07:57 AM

hi, I have got the anwser of aim1:

Code:

SELECT DISTINCT serviceid,tcid,endtime,qos FROM (SELECT * FROM service ORDER BY serviceid, qos, id) AS base GROUP BY serviceid

please help me with aim2, 3 and 4(my final goal)

bbaaking · Apr 18 '12, 06:46 AM

thank you Rabbit, but how to write it, plz help me

bbaaking · Apr 18 '12, 01:31 AM

hi, Rabbit
a nice people give me a SQLServer solution, but I still need a mysql solution. statement of sqlserver is:

Code:

select * from ( select *, row_number() over (partition by [ServiceID] order by Qos) RN from [Service] ) T where RN = 1 order by [ServiceID]

I tested it and I got the correct answer, but I still need a mysql statement, plz give me some advice, how to change it to mysql style?

bbaaking · Apr 17 '12, 05:48 AM

hi,Rabbit
thank you for your answer. but I only need query statement because customer did not allow us to write database. actually, I had suggested to add a field "ID", It is a big concession to the client. We are BPO and working on the business online database(so I cant add a stored procedure to the database).

I think the correct answer is below (but I cant write it with SQL):

Code:

2000	2	Jacob	2011/1/1

...

User Profile

Profile Sidebar

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

complicated SQL:how to using a query to compute rank of order(Outstanding of Class 1)

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

How to filtrate duplicate data about technical support and compute rank of order