CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Kevin Worthington
Kevin Worthington

Reputation: 457

SQL Query/Sub-query help needed

I am trying to get a list of the last 10 Serial numbers from a table of Test information on SQL Server 2005. I tried something like this:

SELECT DISTINCT TOP (10) Serial, DateTime
FROM [Test].[dbo].[TestInfo]
WHERE (TestedBy = 'JSMITH') ORDER BY DateTime DESC

which returns duplicate Serials:

+---------+-------------------------+
| Serial  | DateTime                |
+-----------------------------------+
| 1114048 | 2011-03-16 11:03:14.000 |
| 1617683 | 2011-03-11 15:07:29.000 |
| 1617683 | 2011-03-11 15:07:27.000 |
| 1617683 | 2011-03-11 15:07:26.000 |
| 1617683 | 2011-03-10 13:16:04.000 |
| 1617683 | 2011-03-10 13:15:35.000 |
| 1617683 | 2011-03-10 13:15:30.000 |
| 1617683 | 2011-03-07 13:42:48.000 |
| 1617683 | 2011-03-07 13:40:32.000 |
| 1617683 | 2011-03-07 13:37:58.000 |
+---------+-------------------------+

Is there a way, either using a query or sub-query to get the last 10 Serials without duplicates?

Upvotes: 1

Views: 166

Answers (4)

Tom Micheline
Tom Micheline

Reputation: 911

Probably something like:

SELECT DISTINCT TOP (10) Serial, DateTime
FROM (
  SELECT Serial, MAX(DateTime) AS DateTime
  FROM [Test].[dbo].[TestInfo]
  WHERE (TestedBy = 'JSMITH') 
  GROUP BY Serial
) AS sub
ORDER BY DateTime DESC

Upvotes: 1

John Hartsock
John Hartsock

Reputation: 86892

SELECT TOP 10 
  * 
FROM (SELECT 
        Serial, 
        MAX(DateTime) AS DateTime
      FROM [Test].[dbo].[TestInfo]
      WHERE (TestedBy = 'JSMITH') 
      GROUP BY Serial) q1
ORDER BY q1.DateTime DESC

Upvotes: 1

Faber
Faber

Reputation: 2194

it return duplicate rows beacuse the datetime is different for each row. if you need only the serial field you must write only Serial field in the query.

Upvotes: -1

Aducci
Aducci

Reputation: 26694

select top (10) Serial, Max(DateTime)
from [Test].[dbo].[TestInfo]
where (TestedBy = 'JSMITH')
group by Serial
order by Max(DateTime) desc

Upvotes: 5

Related Questions