Query to bring top latest record

Question

Query to bring top latest record

sqlqueue

SSC Eights!

Points: 922
More actions
April 14, 2009 at 5:45 pm

#220130

Hi Experts,
can you confirm the query to pull out only one latest record for each name descending with date so in below case i just want to pull Harry record of date 2009-01-05 and Robert record of 2009-01-03?
Name Date
Harry2009-01-05
Harry2009-01-02
Harry2009-01-01
Robert2009-01-03
Robert2009-01-01

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 1

sqlqueue (4/14/2009)
Hi Experts,
can you confirm the query to pull out only one latest record for each name descending with date so in below case i just want to pull Harry record of date 2009-01-05 and Robert record of 2009-01-03?
Name Date
Harry2009-01-05
Harry2009-01-02
Harry2009-01-01
Robert2009-01-03
Robert2009-01-01

Select Name, Max(date) as Date

From TableA

Group By Name

Order by Name Desc

[/Code]

That should get you what you need unless I am completely misunderstanding what you are trying to accomplish above.

Fraggle

jchandramouli SSCommitted Points: 1772 More actions · Answer 2

In addition to fraggle's solution, in case if you need the latest date inserted with respect to name considering the dates could be random instead of descending then the following query might help you.

CREATE TABLE #TEMP(UID INT IDENTITY ,NAME VARCHAR(10),MYDATE DATETIME)

INSERT INTO #TEMP SELECT 'Harry','2009-01-05'

INSERT INTO #TEMP SELECT 'Harry','2009-01-02'

INSERT INTO #TEMP SELECT 'Harry','2009-01-15'

INSERT INTO #TEMP SELECT 'Robert','2009-01-03'

INSERT INTO #TEMP SELECT 'Robert','2009-01-01'

SELECT * FROM #TEMP

SELECT T.* FROM #TEMP T

INNER JOIN(SELECT MAX(UID) UID, NAME FROM #TEMP GROUP BY NAME) A ON A.UID = T.UID

DROP TABLE #TEMP

Thanks

"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin

arun.sas SSChampion Points: 11831 More actions · Answer 3

jchandramouli (4/14/2009)
In addition to fraggle's solution, in case if you need the latest date inserted with respect to name considering the dates could be random instead of descending then the following query might help you.
CREATE TABLE #TEMP(UID INT IDENTITY ,NAME VARCHAR(10),MYDATE DATETIME)
INSERT INTO #TEMP SELECT 'Harry','2009-01-05'
INSERT INTO #TEMP SELECT 'Harry','2009-01-02'
INSERT INTO #TEMP SELECT 'Harry','2009-01-15'
INSERT INTO #TEMP SELECT 'Robert','2009-01-03'
INSERT INTO #TEMP SELECT 'Robert','2009-01-01'
SELECT * FROM #TEMP
SELECT T.* FROM #TEMP T
INNER JOIN(SELECT MAX(UID) UID, NAME FROM #TEMP GROUP BY NAME) A ON A.UID = T.UID
DROP TABLE #TEMP
Thanks

Hi Chandramouli,

How your statement works with the original posting?

-- your statement

SELECT T.* FROM #TEMP T

INNER JOIN(SELECT MAX(UID) UID, NAME FROM #TEMP GROUP BY NAME) A ON A.UID = T.UID

RESULT

UIDNAMEMYDATE

3Harry2009-01-15 00:00:00.000

5Robert2009-01-01 00:00:00.000

---Fraggle statement

Select Name, Max(MYDATE) as Date

From #TEMP

Group By Name

Order by Name Desc

RESULT

NameDate

Robert2009-01-03 00:00:00.000

Harry2009-01-15 00:00:00.000

ARUN SAS

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

There are basically three ways you can do it; MAX, TOP, and ROW_NUMBER. They all work. They all don't work equally well. I wrote an article, with code examples, comparing the three. It's available here, the article called, The Questions.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

peggyah SSC Rookie Points: 46 More actions · Answer 5

I have a similar problem where I have records of individuals that are being tracked as they move through an area. I only what the see the last place that they have been, but I'm using a smalldate datatype. Will this still work?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

peggyah (4/22/2009)
I have a similar problem where I have records of individuals that are being tracked as they move through an area. I only what the see the last place that they have been, but I'm using a smalldate datatype. Will this still work?

Start a new thread, peggyah. Can you post a table create statement with some inserts to populate the table with sample data? This will help people to model your problem and provide you with a practical solution. The link in my sig will show you how to do this.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

peggyah SSC Rookie Points: 46 More actions · Answer 7

I tried the MAX function on my data table

SELECT TOP 100 MAX(TriageTagNumber) AS [Triage Tag], Location, [Currect Condition], ArrivalTime

FROM dbo.[Patient Location]

GROUP BY ArrivalTime, [Currect Condition], Location

ORDER BY MAX(TriageTagNumber)

But I get all transactions instead of just the latest Arrival time for each TriageTagNumber

Triage Tag Location Current Condition ArrivalTime

001 Delayed 4/23/2009 7:48:54 AM

001 MS5 Stable4/23/2009 7:53:01 AM

002 ICU Critical4/23/2009 7:53:23 AM

002 Immediate 4/23/2009 7:49:05 AM

003 Delayed 4/23/2009 7:49:11 AM

004 MS5 Guarded4/23/2009 7:53:40 AM

005 OR Stable4/23/2009 7:53:57 AM

005 Expectant/Deceased 4/23/2009 7:49:18 AM

006 Minor/Walk Wounded 4/23/2009 7:49:25 AM

006 Family Medicine Good4/23/2009 7:54:20 AM

008 Family Medicine Good4/23/2009 7:54:46 AM

008 Triage 4/23/2009 7:49:32 AM

I just want to see (Please Help)