sql server programming: Hi

Try thi

select top 1 * from table where order_date=(select max(order_date) from table

Regard
Anjan

Hi

Try this

select top 1 * from table where order_date=(select max(order_date) from table

Regard
Anjan

Hello,

What is the best way to handle a query where I want all the data values from
a row in a table that has the most recent order_date?

If my table has order_date, order_num, order_address for instance. It is
made more difficult by the fact that there can be more than on row that has
the same order_date. In that case, I don't care which one I get back...as
long as I get back only one.

Thanks in advance for the help.

Paul

David,

Thanks...you have helped me before...and I appreciate the help again. I
am taking your approach and not the TOP 1...although that would work...I
also have a requirement to return the oldest order as well which would
let me use MIN in the same fashion you recommeneded the MAX.

Regards,

Paul Reed
www.jacksonreed.com

*** Sent via Developersdex http://www.developersdex.com ***

What is the primary key of this table? The key will help you identify a
unique row to return. I'll guess that the key is order_num. If you post DDL
for your table it helps make these facts clear:

CREATE TABLE Orders (order_num INTEGER PRIMARY KEY, order_address
VARCHAR(10) NOT NULL, order_date DATETIME NOT NULL)

Here's a query that will return one row with the latest date:

SELECT order_num, order_address, order_date
FROM Orders
WHERE order_num =
(SELECT MAX(order_num)
FROM Orders
WHERE order_date =
(SELECT MAX(order_date)
FROM Orders))

Hope this helps.

--
David Portas
SQL Server MVP
--

If you use TOP for this query I suggest you also use ORDER BY and include
the key in the ORDER BY list. TOP without ORDER BY may not give consistent,
repeatable results. Although Paul may not care which row he gets, good
practice for testable code dictates that the result should at least be
repeatable.

SELECT TOP 1 order_num, order_address, order_date
FROM Orders
ORDER BY order_date DESC, order_num DESC

--
David Portas
SQL Server MVP
--