Select DISTINCT issue (SQL Expert needed)

[BP Prgm]

I have a database that I'm pulling out to an ASP page... I am looking for a
wildcard string in a field. If the wildcard is found I just want ONE object
(the one with the latest date) with the same name found. EXAMPLE:



Table: DataVars

Name QCode Alt TimeStamp

John Q1324 HHH 9/25/03 12:01:09PM
John Q9232 GGG 9/25/03 12:12:44PM
Mark Q7326 JJJ 9/25/03 12:11:56PM
Mike Q7322 III 9/25/03 12:08:42PM
Mike Q3211 AAA 9/25/03 12:07:21PM
Kelly Q6433 XXX 9/25/03 12:05:19PM

'what I want (No Kelly because her QCode doesn't include a "32" in it)

John Q1324 HHH 9/25/03 12:01:09PM
Mark Q7326 JJJ 9/25/03 12:11:56PM
Mike Q3211 AAA 9/25/03 12:07:21PM

'These statements don't work

SELECT * FROM Datavars WHERE Name = (SELECT DISTINCT Name FROM Datavars
WHERE QCode LIKE '%32%') ORDER BY TimeStamp DESC;
SELECT DISTINCT Name,QCode,Alt,TimeStamp FROM Datavars WHERE QCode LIKE
'%32%' ORDER BY TimeStamp DESC;



Thanks a billion in advance!!

[Ray at]

When you say you want just one object, do you mean just one record? In your
sample resultset, you have three records. I will assume you want just one.
If so, this should do it.

"SELECT TOP 1 [Name],[QCode],[Alt],[TimeStamp] FROM [Datavars] WHERE [QCode]
LIKE '%32%' ORDER BY [TimeStamp]

Ray at home


"BP Prgm" <nospam@please.com> wrote in message
news:OsNU%233HiDHA.604@TK2MSFTNGP10.phx.gbl...

> I have a database that I'm pulling out to an ASP page... I am looking for

a

> wildcard string in a field. If the wildcard is found I just want ONE

object

> (the one with the latest date) with the same name found. EXAMPLE:
>
>
>
> Table: DataVars
>
> Name QCode Alt TimeStamp
>
> John Q1324 HHH 9/25/03 12:01:09PM
> John Q9232 GGG 9/25/03 12:12:44PM
> Mark Q7326 JJJ 9/25/03 12:11:56PM
> Mike Q7322 III 9/25/03 12:08:42PM
> Mike Q3211 AAA 9/25/03 12:07:21PM
> Kelly Q6433 XXX 9/25/03 12:05:19PM
>
> 'what I want (No Kelly because her QCode doesn't include a "32" in it)
>
> John Q1324 HHH 9/25/03 12:01:09PM
> Mark Q7326 JJJ 9/25/03 12:11:56PM
> Mike Q3211 AAA 9/25/03 12:07:21PM
>
> 'These statements don't work
>
> SELECT * FROM Datavars WHERE Name = (SELECT DISTINCT Name FROM Datavars
> WHERE QCode LIKE '%32%') ORDER BY TimeStamp DESC;
> SELECT DISTINCT Name,QCode,Alt,TimeStamp FROM Datavars WHERE QCode LIKE
> '%32%' ORDER BY TimeStamp DESC;
>
>
>
> Thanks a billion in advance!!
>
>

[Bob Barrows]

I'm assuming you want to return the records with the earliest timestamp for
each name. I am also going to assume you will never have two records for the
same name with identical timestamps.

Start by creating a query that returns only the records with the correct
QCodes:

Select [Name],[TimeStamp] from datavars
where QCode like '%32%'

Now use that query as the data source for a query that returns the minimum
timestamp for each name:

Select [Name],Min([TimeStamp]) As minTimeStamp
FROM (
Select [Name],[TimeStamp] from datavars
where QCode like '%32%') As q1
GROUP BY [Name]

Now join this query back to the original table so you can get the rest of
the information required:

Select q2.[Name],t.QCode,t.Alt,q2.minTimestamp As [TimeStamp]
FROM datavars t INNER JOIN (
Select [Name],Min([TimeStamp]) As minTimeStamp
FROM (
Select [Name],[TimeStamp] from datavars
where QCode like '%32%') As q1
GROUP BY [Name]) As q2
ON t.[Name] = q2.Name AND t.[Timestamp] = q2.minTimeStamp

HTH,
Bob Barrows

BP Prgm wrote:

> I have a database that I'm pulling out to an ASP page... I am looking
> for a wildcard string in a field. If the wildcard is found I just
> want ONE object (the one with the latest date) with the same name
> found. EXAMPLE:
>
>
>
> Table: DataVars
>
> Name QCode Alt TimeStamp
>
> John Q1324 HHH 9/25/03 12:01:09PM
> John Q9232 GGG 9/25/03 12:12:44PM
> Mark Q7326 JJJ 9/25/03 12:11:56PM
> Mike Q7322 III 9/25/03 12:08:42PM
> Mike Q3211 AAA 9/25/03 12:07:21PM
> Kelly Q6433 XXX 9/25/03 12:05:19PM
>
> 'what I want (No Kelly because her QCode doesn't include a "32" in it)
>
> John Q1324 HHH 9/25/03 12:01:09PM
> Mark Q7326 JJJ 9/25/03 12:11:56PM
> Mike Q3211 AAA 9/25/03 12:07:21PM
>
> 'These statements don't work
>
> SELECT * FROM Datavars WHERE Name = (SELECT DISTINCT Name FROM
> Datavars WHERE QCode LIKE '%32%') ORDER BY TimeStamp DESC;
> SELECT DISTINCT Name,QCode,Alt,TimeStamp FROM Datavars WHERE QCode
> LIKE '%32%' ORDER BY TimeStamp DESC;
>
>
>
> Thanks a billion in advance!!