Query Help 3

[scotttom]

Hi,

I am looking for a single result from this query. I need the ONE record with the smallest date.

My table looks something like this...

Project SYSDATE SYSComp
ABC123 1/1/2008 1
ABC123 2/1/2008 0

And here is my query...

SELECT     Project, MIN(SYSDate), SYSComp
FROM         dbo.Activity
GROUP BY Code, SYSComp, client, Project
HAVING      (client LIKE '%STUFF%') AND (Code = '9')

This query unfortunately outputs both records.

Thanks very much in advance for any help.

Scott

 

[gmmastros]

SELECT [!]Top 1[/!] Project, SYSDate, SYSComp
FROM   dbo.Activity
WHERE  (client LIKE '%STUFF%') AND (Code = '9')
Order By SysDate

Depending on your requirements, you may want to try...

[tt][blue]Order By SysDate [red]DESC[/red][/blue][/tt]

Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[scotttom]

Thank you for the reply George. But I left out a detail.

I actually need a list of the first occurrences for each project.

My table actually looks like this....

Project SYSDATE SYSComp
ABC123 1/1/2008 1
ABC123 2/1/2008 0
DEF456 1/1/2008 1
DEF456 2/1/2008 0

I need the following returned..

Project SYSDATE SYSComp
ABC123 1/1/2008 1
DEF456 1/1/2008 1

I'm guessing I need some sort of a subquery where I put a query in the FROM clause. Those always stump me.

Thanks again in advance for any help.

Scott

 

[gmmastros]

>> Those always stump me.

Well... It's time you learn.

As such, let's build this in stages so that it's easier to understand. In the code I show below, I create a table variable with some hard-coded values. This will be the basis for the queries I show. This means you can copy/paste to query analyzer to see how it works.

First, the table variable.

Declare @Temp Table(Project VarChar(20), SysDate DateTime, SysComp Bit)

Insert Into @Temp Values('ABC123','1/1/2008',1)
Insert Into @Temp Values('ABC123','2/1/2008',0)
Insert Into @Temp Values('DEF456','1/1/2008',1)
Insert Into @Temp Values('DEF456','2/1/2008',0)

Now, let's build a query that returns the project and minimum sysdate. This is relatively straight forward.

Select Project, Min(SysDate) As MinSysDate
From   @Temp
Group By Project

When you run this, you'll see that there is 1 row returned for each project. For the next step, we make this query become a derived table that we join back to the original. The trick here is that we will use the MinSysDate as a join condition, which effectively filters out unwanted rows.

Select A.*
From   @Temp As A
       Inner Join [!]([/!]
         [blue]Select Project, Min(SysDate) As MinSysDate
         From   @Temp
         Group By Project[/blue]
         [!]) As B[/!]
         On A.Project = B.Project
         And A.SysDate = B.MinSysDate

Notice the part in blue, that is the original query. To make this become a derived table, you need to add the parts in red. Since this is an inner join, you also need the ON clause to identify how the table and the derived table are joined.

It's very important that you learn this because once you master derived tables, you will be able to create better queries that perform MUCH better.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[scotttom]

Thank you George!

That is fantastic. I really appreciate you taking the time to break it down for me.

Scott

 

[gmmastros]

You're welcome.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ESquared]

Just in case you're interested, there IS another way, but you should use it only if the extra complexity and confusion are worth it (and it can be easy to go wrong with this and get wrong answers).

SELECT
   Project,
   SysDate = Min(SysDate),
   SysComp = Convert(int, Substring(Min(Convert(binary(8), SysDate) + Convert(binary(4), SysComp)), 9, 4))
FROM Projects
GROUP BY Project

I assumed a data type for SysComp which might be wrong. You'll have to use some knowledge to convert whatever data type it is to an appropriate sortable data type.

You don't have to use binary--you can use char--but the converted data has to sort the same way as the original data, and you have to be sure that the converted-back value is always the same as the original. If it was a decimal or float column you could not convert it to binary because those are packed data types and don't sort properly.

For example, if your original SysComp is a varchar(10) column that can range in length from 1 to 10 characters, and you use binary(10) for it, your output strings won't match because they'll pick up trailing ascii character zeroes.

Also for binary, this is now straight binary sort order which will pay no attention to collation and localization. Binary is good for date and int data types, perhaps varchar is best for everything else (and make sure you pick a conversion from date that sorts properly).

It is confusing enough and has enough potential for mistakes that it probably is something best treated as a curiosity, but I like using it, personally (ha).

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]