SQL Query/Sub-query help needed

Question

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?

Aducci · Accepted Answer · 2011-03-24 16:11:21Z

5

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

answered Mar 24, 2011 at 16:11

Aducci

26.8k8 gold badges67 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kevin Worthington Over a year ago

I accepted your answer, but I removed the Max(DateTime) from the SELECT portion, since I really just need the list of Serials: SELECT TOP (10) Serial FROM [Test].[dbo].[TestInfo] WHERE (TestedBy = 'JSMITH') GROUP BY Serial ORDER BY Max(DateTime) DESC

John Hartsock · Accepted Answer · 2011-03-24 16:11:52Z

1

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

answered Mar 24, 2011 at 16:11

John Hartsock

87.1k23 gold badges135 silver badges146 bronze badges

Comments

Tom Micheline · Accepted Answer · 2011-03-24 16:11:54Z

1

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

answered Mar 24, 2011 at 16:11

Tom Micheline

9211 gold badge5 silver badges5 bronze badges

Comments

Faber · Accepted Answer · 2011-03-24 16:11:44Z

-1

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.

answered Mar 24, 2011 at 16:11

Faber

2,2042 gold badges28 silver badges37 bronze badges

Collectives™ on Stack Overflow

SQL Query/Sub-query help needed

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related