Author |
Topic: SQL Query (MySQL) (Read 614 times) |
|
Sameer
Uberpuzzler
Pie = pi * e
Gender: 
Posts: 1261
|
 |
SQL Query (MySQL)
« on: Sep 5th, 2007, 10:32pm » |
 Quote  Modify
|
Anyone good with SQL? I am having trouble with a query!!
I have a table as follows
Tablename: information
Columns: id (incremented everytime a row is inserted)
name: name of ticket
status: (values are open, fixed)
date: date status changed
E.g.
id/name/status (ignoring date for now)
1/aa/open
2/aa/fixed
3/aa/open <-- the same ticket can reopen
4/ab/open
10/aa/fixed <-- maybe other tickets have been updated between row with id 3 and 10
so you see we don't really have any correlation with id and ticket statuses.
Let's say I have these current rows:
id/name/status
40/aa/open
45/aa/open <-- somebody just updated the ticket will insert a row but status remains open
53/aa/fixed
65/aa/fixed <-- update
I want to detect the row the status changed from open to fixed.
The result I want is 53/aa/fixed using a query. Of course I want it over all the ticket names. But only the times when the status changed!! How do I go about doing this? (I put this question on MYSQL forum too; let's see if one of you gets this quickly!)
|
|
 IP Logged |
"Obvious" is the most dangerous word in mathematics.
--Bell, Eric Temple
Proof is an idol before which the mathematician tortures himself.
Sir Arthur Eddington, quoted in Bridges to Infinity
|
|
|
TenaliRaman
Uberpuzzler
I am no special. I am only passionately curious.
Gender:
Posts: 1001
|
|
Re: SQL Query (MySQL)
« Reply #1 on: Sep 6th, 2007, 12:17am » |
Quote Modify
|
Simple way. Write a small java app which connects to your database and retrieves all tuples and does your job.
Now if you want to do the same with sql, then i am a bit skeptical. But lets see :
1. Create view of all the tuples that are open (call it openView)
2. Create view of all the tuples that are fixed (call it fixedView)
3. Run a select query on the two views such that openView .id + 1 == fixedView.id
-- AI
|
|
IP Logged |
Self discovery comes when a man measures himself against an obstacle - Antoine de Saint Exupery
|
|
|
towr
wu::riddles Moderator
Uberpuzzler
Some people are average, some are just mean.
Gender:
Posts: 13730
|
|
Re: SQL Query (MySQL)
« Reply #2 on: Sep 6th, 2007, 12:52am » |
Quote Modify
|
It seems you want all open tickets with an id such that there is a closed ticket with the same name but a later id.
something like
SELECT id,name,status FROM information WHERE status='open' AND EXISTS (SELECT id as id2,name as name2,status as status2 FROM information WHERE name=name2 AND status='closed' AND id < id2);
See also
http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries. html
|
|
IP Logged |
Wikipedia, Google, Mathworld, Integer sequence DB
|
|
|
SMQ
wu::riddles Moderator
Uberpuzzler
Gender:
Posts: 2084
|
|
Re: SQL Query (MySQL)
« Reply #3 on: Sep 6th, 2007, 7:31am » |
Quote Modify
|
Actually, I think he wants all records where the previous status for a ticket with the same name is different from the current status:
SELECT * FROM information a WHERE a.status <> (SELECT status FROM information b WHERE b.id = (SELECT MAX(id) FROM information c WHERE c.name = a.name AND c.id < a.id))
(The above query will also return the first entry for each ticket, by virtue of there being no previous row with the same name so b.status is NULL)
--SMQ
|
|
IP Logged |
--SMQ
|
|
|
towr
wu::riddles Moderator
Uberpuzzler
Some people are average, some are just mean.
Gender:
Posts: 13730
|
|
Re: SQL Query (MySQL)
« Reply #4 on: Sep 6th, 2007, 7:48am » |
Quote Modify
|
on Sep 6th, 2007, 7:31am, SMQ wrote:| Actually, I think he wants all records where the previous status for a ticket with the same name is different from the current status |
|
Or something else still:
on Sep 5th, 2007, 10:32pm, Sameer wrote:| I want to detect the row the status changed from open to fixed. |
|
Only a small change though.
|
|
IP Logged |
Wikipedia, Google, Mathworld, Integer sequence DB
|
|
|
SMQ
wu::riddles Moderator
Uberpuzzler
Gender:
Posts: 2084
|
|
Re: SQL Query (MySQL)
« Reply #5 on: Sep 6th, 2007, 7:54am » |
Quote Modify
|
He also said:
on Sep 5th, 2007, 10:32pm, Sameer wrote:| But only the times when the status changed |
|
So it's not entirely clear whether he wants all status changes or just status changes from open to fixed. In the latter case, modify the above to SELECT * FROM information a WHERE a.status = 'fixed' AND a.status <> ...
--SMQ
|
|
IP Logged |
--SMQ
|
|
|
Sameer
Uberpuzzler
Pie = pi * e
Gender:
Posts: 1261
|
|
Re: SQL Query (MySQL)
« Reply #6 on: Sep 6th, 2007, 9:41am » |
Quote Modify
|
on Sep 6th, 2007, 7:54am, SMQ wrote:He also said:
So it's not entirely clear whether he wants all status changes or just status changes from open to fixed. In the latter case, modify the above to SELECT * FROM information a WHERE a.status = 'fixed' AND a.status <> ...
--SMQ |
|
Ok here's some extra parameters for you!! The data ran is limited to 2 weeks. So check on 2 weeks from current date!! THat part is easy!!
In this two week period however you can have multiple status changes.. something like
10/open
20/open
30/close
40/close
50/close
60/open
70/close
In this case however I just want to count the status change from open to close once!!!
So consider a table like this
id/name/status
1/xx/open
2/xx/open
3/yy/open
4/zz/open
5/xx/open
6/xx/close
7/zz/close
8/zz/close
9/zz/close
10/zz/close
11/yy/open
12/yy/close
13/zz/close
14/xx/close
15/xx/open
16/xx/close
Output:
7/zz/close
12/yy/close
16/xx/close
|
|
IP Logged |
"Obvious" is the most dangerous word in mathematics.
--Bell, Eric Temple
Proof is an idol before which the mathematician tortures himself.
Sir Arthur Eddington, quoted in Bridges to Infinity
|
|
|
towr
wu::riddles Moderator
Uberpuzzler
Some people are average, some are just mean.
Gender:
Posts: 13730
|
|
Re: SQL Query (MySQL)
« Reply #7 on: Sep 6th, 2007, 10:00am » |
Quote Modify
|
So within the past two weeks, you want for each name, the last close not followed by another close of the same name?
I can't remember how to check dates properly though..
|
|
IP Logged |
Wikipedia, Google, Mathworld, Integer sequence DB
|
|
|
SMQ
wu::riddles Moderator
Uberpuzzler
Gender:
Posts: 2084
|
|
Re: SQL Query (MySQL)
« Reply #8 on: Sep 6th, 2007, 10:08am » |
Quote Modify
|
The MySQL syntax for dates may be a bit different, but:
SELECT * FROM information a WHERE a.date >= DATEADD(WEEK, -2, CURDATE()) AND a.status <> (SELECT status FROM information b WHERE b.id = (SELECT MAX(id) FROM information c WHERE c.name = a.name AND c.id < a.id)) AND NOT EXISTS (SELECT * FROM information d WHERE d.name = a.name AND d.status <> a.status AND d.id > a.id)
(and again, insert AND a.status = 'closed' if you're only interested in the closings and not all status changes.)
--SMQ
|
| « Last Edit: Sep 6th, 2007, 10:09am by SMQ » |
IP Logged |
--SMQ
|
|
|
|
RIDDLES SITE
WRITE MATH!
Home 
Help 
Search 
Members 
Login 
Register
Reply
Notify of replies
Send Topic
Print