CODEWITHSUNDEEP
sqldatemin
nachoargentina
nachoargentina

Reputation: 107

Select the record with the MIN(DATE)

So, a very simple query, but I'm not sure if I got it right since I'm making this on paper and don't have a DBMS where I can try it on. It's an exercise for a college exam. I have this table:

    Serv_req
    =============
PK  Svr_ID
    State
    Req_Date
    Description

They are simply asking me to show the data for the oldest Serv_Req that has "Open" as state.

What I wrote so far is:

SELECT Svr_ID, State, Description, MIN(Req_Date)
FROM Serv_req
WHERE State LIKE 'Open'
GROUP BY Svr_ID, State, Description

Now, will this return ONLY the record with the info for the oldest Svr_req? or will it return a list of ALL "Open" requirements with just the date of the oldest one at the end?

UPDATE

I forgot to mention. Unfortunatelly, course rules don't allow us to use SELECT TOP, LIMIT 1, or even subqueries to create aux tables in the FROM statement.

Thanks in advance for your help guys!

UPDATE 2 Ok, so, after seeing the first query I wrote is totally wrong, I'm giving it a second try. Please tell me if you think this is right:

SELECT Svr_id, State, Description
FROM Serv_req
WHERE State LIKE 'Open'
AND Req_Date = (SELECT MIN(S1.Req_date)
                FROM Serv_req S1
                WHERE S1.State LIKE 'Open'
                )

I know this is not the clearest/most performing way to do this query, but with the limitation my course imposes, it's the best I could come up with. What do you guys think? Does it seem right?

Upvotes: 3

Views: 5581

Answers (3)

KISS
KISS

Reputation: 81

Looking at this another way, the question asks for the oldest Serv_Req. Taking that literally, the result is a single record i.e. the oldest open one. That being the case, the very first attempt ("What I wrote so far is:") without the Group By clause would give the answer. KISS - Keep it Stupendously Simple

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 727097

EDIT Since you cannot use the TOP 1 trick or subqueries, you need to come up with a condition that spells out the definition of MIN using the elementary "less then" relations.

Think about the problem in terms of comparing each record to all records in the same table. You can do it by building a self-join such that all records that you select from the table that you join with have an earlier date. There will be some records for which it is not possible to find a pair with a strictly earlier date; that is the record that you want. Obviously, you need an outer join to bring the base record into the results, because the match does not exist. The record that you are looking for will have a unique structure letting you identify it among the records that you do not want to see.

Upvotes: 1

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

Nice to see someone else from ARG :)

Well, basically your query doesn't work. You need to remove your second SELECT, but just the keyword, not the fields. As of the question you're asking I think you're not quite getting the GROUP BY.

The GROUP BY in this case will take all Svr_ID, State, Description that are distinct (i.e. that make a unique tuple) and the MIN(Req_Date) will take the minimum from the elements that where grouped by for each of the distinct touples.

Now, if Svr_ID is a PK your GROUP BY won't actually group anything because the Svr_ID is "distinct" in itself, regardless of the state and description fields. That means that query is the same as this query:

SELECT Svr_ID, State, Description, Req_Date
FROM Serv_req
WHERE State LIKE 'Open'

Is it a bit more clear?

Edit:

Now, you said you didn't have a DBMS where you can try those queries out, so let me introduce you to SQLFiddle. I'd recommend you any DBMS but Oracle because the syntax to create new tables and insert records might be a little bit unfriendly.

Now I'm also adding one answer to your question here. I'm not pasting the answer so that you take your time and play with it. The tables ready for you to play with are here, so this should be your starting point to test your queries... but remember, always try to solve them on paper first... you won't be able to test them in the exam :)

Upvotes: 4

Related Questions