• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 576
  • Last Modified:

A difficult SQL query

I have a table like following, it has three columns:
ID          Status                Date
1             13        3/8/2007 18:55:58
2             14        3/9/2007 19:00:00
3             13        3/10/2007 19:00:00
4             13        3/11/2007  19:00:00
I want to make a sql query to get the following record:
status 13 insert to table after status 14(base on date column), such as ID 3 record  in above sample table.
and if the lastest record is not 14, like row 4, the lastest record is 13, so row 4 shouldn't be selected.
In sample table, only row 3 should be selected.
I am not sure I have explained it very clear, pls. help me. Thanks a lot!
btw, server is sql-server, so t-sql can be used.
0
d_chenxin
Asked:
d_chenxin
1 Solution
 
JakeAnvilCommented:
It is a bit tricky to understand. I suggest you make up a table of data that should be retrieved by the query in subject. Just the way you typed in the initial data table in your question. More details on what you want to accomplish could be helpfull, too.
0
 
auke_tCommented:
Something like this I think:
SELECT
	*
FROM
	yourtable t3
WHERE
	t3.[date] in
(
SELECT
	(SELECT min(t2.[date]) FROM yourtable t2 WHERE t2.[date] > t1.[date]
FROM
	yourtable t1
WHERE
	status = 14
)
AND
	status = 13

Open in new window

0
 
Rajesh_mjCommented:

SELECT ID,Status,DATE from
MyTable M where M.ID >=
 (Select min(id) from Mytable M1 where M1.Status = 
(select Max(M2.Status) from MyTable M2))

Open in new window

0
 
auke_tCommented:
Forgot one )
CREATE TABLE #yourtable
(
ID integer,
Status integer,
Date DATETIME
)
INSERT INTO #yourtable VALUES (1, 13, '3/8/2007 18:55:58')
INSERT INTO #yourtable VALUES (2, 14, '3/9/2007 19:00:00')
INSERT INTO #yourtable VALUES (3, 13, '3/10/2007 19:00:00')
INSERT INTO #yourtable VALUES (4, 13, '3/11/2007  19:00:00')
 
SELECT
	*
FROM
	#yourtable t3
WHERE
	t3.[date] in
(
SELECT
	(SELECT min(t2.[date]) FROM #yourtable t2 WHERE t2.[date] > t1.[date]) 
FROM
	#yourtable t1
WHERE
	status = 14
)
AND
	status = 13

Open in new window

0
 
d_chenxinAuthor Commented:
Hi auke_t,

Thank you so much for your solution, it's very good!
I want to accept your solution, but it always shows an error: EmailTemplateTag not found (Tag: GRADING_COMMENTS)
So do you know what's wrong with me? Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now