how to get only latest date and other columns from table

Question

how to get only latest date and other columns from table

shyam.oec

Old Hand

Points: 380
More actions
April 14, 2010 at 6:56 pm

#144107

Hi,
Table
id name date
123 a 2/3/2010
124 b 15/2/2010
125 c 1/1/2010
123 a 12/4/2010
125 c 17/2/2010
this is a table, here id,name,date is composite key.
I want to select id,name and date so that latest date (e.g., for id 123 date 12/4/2010 be selected) is selected for each id and each id and name should be selected once.
So what will be its query?
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

shyam.oec Old Hand Points: 380 More actions · Answer 2

Hi,

actually concept behind this type of problem is new for me..i am confused how to start my query...

shyam.oec Old Hand Points: 380 More actions · Answer 3

Hi,

this query is giving error :

select id,name,dt from (select id,name,dt, max(dt) over (partition by id, name)
as max_date from temp2) where dt=max_date;

this query is fine ,but i have to select each id and name only once

select id,name,dt, max(dt) over (partition by id, name)
as max_date from temp2

So,how to select rows such that dt=max_date?

Thanks

WayneS SSC Guru Points: 95460 More actions · Answer 4

See if this gets what you need.

-- start off with the test data

-- See how you start off by actually creating a table and then

-- inserting the data into it? Your doing this makes it a lot easier

-- for all of us volunteers to help you. So, help us help you.

-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/

-- for more details on how to do all of this.

-- since your dates are in DMY format, and most of us in the USA use MDY,

-- start off by ensuring that the data can be loaded properly by setting

-- the appropriate date format!

SET DATEFORMAT DMY

DECLARE @test-2 TABLE (id int, name char(1), [date] datetime)

INSERT INTO @test-2

SELECT 123, 'a', '2/3/2010' UNION ALL

SELECT 124, 'b', '15/2/2010' UNION ALL

SELECT 125, 'c', '1/1/2010' UNION ALL

SELECT 123, 'a', '12/4/2010' UNION ALL

SELECT 125, 'c','17/2/2010'

-- now run the query

SELECT id, name, dt=max([date])

FROM @test-2

GROUP BY id, name

If this doesn't return the results that you want, then show us what your expected results are based on the sample data.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

shyam.oec Old Hand Points: 380 More actions · Answer 5

no that did't solved my problem.equivalent problem is solved in oracle through the query that i have initially posted .Then how to solve it in SqlServer?

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 6

You can use the ROW_NUMBER() function

; WITH cte_Table AS

(

SELECTROW_NUMBER() OVER ( PARTITION BY id, name ORDER BY date DESC ) Row_Num, *

FROMTable

)

SELECT*

FROMcte_Table

WHERERow_Num = 1

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Ben Teraberry SSChampion Points: 11845 More actions · Answer 7

A simple subquery also works:

SELECT t.id,

t.name,

t.[date]

FROM @test-2 t

WHERE t.[date] = (SELECT MAX(sq.[date]) FROM @test-2 sq WHERE sq.id = t.id and sq.name = t.name)

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Ben Teraberry SSChampion Points: 11845 More actions · Answer 8

By the way, you would really only use the above example with the subquery if there were additional fields that you want to include that are associated with that max date you want to return. If you're really just looking to return the two fields and the max date of those two, you can just do this:

SELECT t.id,

t.name,

MAX(t.[date])

FROM @test-2 t

GROUP BY t.id, t.name

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 9

shyam.oec (4/15/2010)
no that did't solved my problem.equivalent problem is solved in oracle through the query that i have initially posted .Then how to solve it in SqlServer?

Same query - you were just missing the required table alias.

The other solutions presented are more efficient, but for completeness, here is how your query should have looked:

DECLARE @test-2

TABLE (

id INTEGER NOT NULL,

name CHAR(1) NOT NULL,

dt DATETIME NOT NULL

);

INSERT @test-2

(id, name, dt)

SELECT 123, 'a', '2010-03-02T00:00:00.000' UNION ALL

SELECT 124, 'b', '2010-02-15T00:00:00.000' UNION ALL

SELECT 125, 'c', '2010-01-01T00:00:00.000' UNION ALL

SELECT 123, 'a', '2010-04-12T00:00:00.000' UNION ALL

SELECT 125, 'c', '2010-02-17T00:00:00.000';

SELECT SubQuery.id,

SubQuery.name,

SubQuery.dt

FROM (

SELECT T.*,

max_date = MAX(T.dt) OVER (PARTITION BY T.id, T.name)

FROM @test-2 T

) SubQuery

WHERE SubQuery.dt = SubQuery.max_date;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi