Need to take one row with max of id from a table with multiple rows with same data

Question

Post reply

Need to take one row with max of id from a table with multiple rows with same data

dsandhyarao

Say Hey Kid

Points: 683
More actions
March 6, 2010 at 11:30 am

#217498

hi,
i have a table with few columns
as
table source (id,memno,date_trans,name1,name2,x,yz..)
after querying on some conditions i get the result in destination table
Table destination has columns (id int (identity),memNo varchar, date_trans varchar)
the data is coming as
id memno date_trans
1 3456A 11/09/09
2 3456A 11/09/09
3 3456B 12/09/09
4 3456B 14/09/09
5 3456A 10/1/09
6 345D5 10/2/09
7 3456B 14/09/09
now from this table, i need
---only one row which has same memno and date_tran and max of id
---if memno is same for 2 records with date as different they need to stay
like in above i need result as
id memno date_trans
2 3456A 11/09/09
3 3456B 12/09/09
5 3456A 10/1/09
6 345D5 10/2/09
7 3456B 14/09/09
can some one help me out with the query to delete the rows which are matching and have only those needed.
thanks
s

Viewing 5 posts - 1 through 4 (of 4 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

The easiest way is to use GROUP BY

SELECT MAX(id) AS id,memNo,date_trans

FROM @tbl

GROUP BY memNo,date_trans

ORDER BY MAX(id)

Side note: It is bad practice to store date values as varchar, especially when using a format like you did. Example:

'11/09/09' could be September 9th 2011, September 11th 2009, or November 9th 2009. Also, you'll slow down any query against that column if you run any kind of date comparison, since you couldn't benefit from any kind of index that might exist.

I strongly recommend to change the column to DATETIME or, since you're obviously using SS2K8 (you posted in a 2K8 forum), you should use the DATE data type.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

Another method, just because I like segment operators:

DECLARE @T

TABLE (

id INTEGER PRIMARY KEY,

memno CHAR(5) NOT NULL,

date_trans DATETIME NOT NULL,

UNIQUE (memno DESC, date_trans DESC, id DESC)

);

INSERT @T

(id, memno, date_trans)

SELECT 1, '3456A', '20090911' UNION ALL

SELECT 2, '3456A', '20090911' UNION ALL

SELECT 3, '3456B', '20090912' UNION ALL

SELECT 4, '3456B', '20090914' UNION ALL

SELECT 5, '3456A', '20090110' UNION ALL

SELECT 6, '345D5', '20090210' UNION ALL

SELECT 7, '3456B', '20090914';

SELECT T1.id,

T1.memno,

T1.date_trans

FROM @T T1

WHERE T1.id =

(

SELECT MAX(T2.id)

FROM @T T2

WHERE T2.memno = T1.memno

AND T2.date_trans = T1.date_trans

);

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

dsandhyarao Say Hey Kid Points: 683 More actions · Answer 3

thanks a lot guys for directing me.

it helped.

also, i made up the dummy data ,but date field is date type only in my table and not varchar.(thanks for the advice though!)

sunjer777 SSC Journeyman Points: 97 More actions · Answer 4

check this...

select *

from (select row_number() over(partition by memno, date_trans

order by id desc) my_rowid,

id, memno, date_trans

from<tablename> ) a

wheremy_rowid = 1