StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PONeed a Complex SQL Query
text
Body
copied!<p>I need to make a rather complex query, and I need help bad. Below is an example I made.</p> <p>Basically, I need a query that will return one row for each <strong>case_id</strong> where the type is <strong>support</strong>, status <strong>start</strong>, and date meaning the very first one created (so that in the example below, only the 2/1/2009 John's case gets returned, not the 3/1/2009). The search needs to be dynamic to the point of being able to return all similar rows with different case_id's etc from a table with thousands of rows.</p> <p>There's more after that but I don't know all the details yet, and I think I can figure it out if you guys (an gals) can help me out here. :)</p> <pre> ID | Case_ID | Name | Date | Status | Type    48 | 450 | John | 6/1/2009 | Fixed | Support 47 | 450 | John | 4/1/2009 | Moved | Support 46 | 451 | Sarah | 3/1/2009 | | 45 | 432 | John | 3/1/2009 | Fixed | Critical 44 | 450 | John | 3/1/2009 | Start | Support 42 | 450 | John | 2/1/2009 | Start | Support 41 | 440 | Ben | 2/1/2009 | | 40 | 432 | John | 1/1/2009 | Start | Critical ... </pre> <p>Thanks a bunch!</p> <p>Edit:</p> <p>To answer some people's questions, I'm using SQL Server 2005. And the date is just plain date, not string.</p> <p>Ok so now I got further in the problem. I ended up with Bliek's solution which worked like a charm. But now I ran into the problem that sometimes the status never starts, as it's solved immediately. I need to include this in as well. But only for a certain time period.</p> <p>I imagine I'm going to have to check for the case table referenced by FK Case_ID here. So I'd need a way to check for each Case_ID created in the CaseTable within the past month, and then run a search for these in the same table and same manner as posted above, returning only the first result as before. How can I use the other table like that?</p> <p>As usual I'll try to find the answer myself while waiting, thanks again!</p> <p>Edit 2:</p> <p>Seems this is the answer. I don't have access to the full DB yet so I can't fully test it, but it seems to be working with the dummy tables I created, to continue from Bliek's code's WHERE clause:</p> <pre> WHERE RowNumber = 1 AND Case_ID IN (SELECT Case_ID FROM CaseTable WHERE (Date BETWEEN '2007/11/1' AND '2007/11/30')) </pre> <p>The date's screwed again but you get the idea I'm sure. Thanks for the help everyone! I'll get back if there're more problems, but I think with this info I can improvise my way through most of the SQL problems I currently have to deal with. :)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload