Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 570
  • 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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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