Most recent value with WHERE clause - DML (SELECT, INSERT, UPDATE, DELETE) Questions

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » Most recent value with WHERE clause

Most recent value with WHERE clause

Options

Previous Topic · Next Topic

pdbowling

Posted: Friday, January 18, 2008 12:57:08 PM

Rank: Newbie

Joined: 1/18/2008
Posts: 2
Points: 6
Location: Lexington, KY

Hello all, I guess examples are the best way to explain.

myTable
AutoID (int)
Date (DateTime)
GUID (string)
Result (XML)

Some data would look like

12
1/1/08
262f4b20-0b40-4225-a913-40776fe1g3a2
<root server="111.11.11.11">123736</root>

There are many different dates and guids. The guids are process IDs so will occur repeatedly in the guid column.

I want to query out the most recent entry for a given GUID.

SELECT RESULT FROM myTable WHERE
GUID = '262f4b20-0b40-4225-a913-40776fe1g3a2'
AND DATE (is most recent).

I have no idea where to start on syntax for Date most recent or for handlling an XML field.

Any suggestions would be great.

I'm a developer doing what I'm told so I cannot control the database or the requested results.
Thanks all
Patrick

Scott Whigham

Posted: Friday, January 18, 2008 1:33:03 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Hi there-

First I would suggest a little more knowledge of subqueries (such as those at http://www.learntransactsql.com/VideoTutorials/SQL-Videos/76/Subqueries-Nested-Queries-and-Derived-Tables-in.aspx). Second, the query likely would be something like:

SELECT RESULT FROM myTable WHERE
GUID = '262f4b20-0b40-4225-a913-40776fe1g3a2'
AND DATE = (SELECT MAX(DATE) FROM myTable WHERE GUID = '262f4b20-0b40-4225-a913-40776fe1g3a2')

Is this what you need?

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.