Click here to Skip to main content
14,579,188 members
Rate this:
Please Sign up or sign in to vote.
See more:
i have 3 records

Id Name  status      Datetime
1  x      not valid  2020-6-5
2  y      valid      2020-3-1
1  x      valid      2020-6-6


What I have tried:

i want to retrieve the latest created record for each Id

i mean the result should be

2  y     valid
1  x     valid


because 1 x valid was created after 1 x not valid
Posted
Updated 24-Jun-20 20:19pm
v3
Comments
Maciej Los 25-Jun-20 4:37am
   
What database engine: MS SQL SErver, MS Access, MySql, PostgreSql, Oracle, other?
Rate this:
Please Sign up or sign in to vote.

Solution 1

C'mon - you haven't even attempted to write SQL, you're getting us to do your homework or whatever for you....

So what would happen if you wrote

SELECT * from table;


well, clearly, thats gets everything, not what you asked for is it ? .. so how about

SELECT * from table where status != 'not valid';


Well, that's a bit closer , but you're not all the way there, you still need to think about the 'latest' record created - how are you going to handle that - are you going to test or look at the Datetime field ??

Why dont you have a go, and improve your question with what you've got, when you've really tried something and are stuck - we dont do homework for people
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

you can you
ROW_NUMBER()  
function.

declare  @tbl table
(
	Id int,
	Name nvarchar(50),
	[STATUS] nvarchar(50),
	[DateTime] date
)

INSERT INTO @tbl(Id,Name,[STATUS],[DateTime])
VALUES
(1,  'x',     'not valid',  '2020-6-5'),
(2,  'y',     'valid',      '2020-3-1'),
(1,  'x',    'valid',      '2020-6-6')


declare  @tbl2 table
(
	Id int,
	Name nvarchar(50),
	[STATUS] nvarchar(50),
	[DateTime] date,
	RowNumber int
)

INSERT INTO @tbl2(Id,Name,[STATUS],[DateTime],RowNumber)
SELECT *,ROW_NUMBER()   over( partition by  Id order by [DateTime] desc) AS RowNumber  FROM @tbl


SELECT * FROM @tbl2 WHERE [@tbl2].RowNumber=1


output :
Id	Name	STATUS	DateTime	RowNumber
1	x	valid	2020-06-06	1
2	y	valid	2020-03-01	1
   
Comments
CHill60 25-Jun-20 4:25am
   
Not sure why this was downvoted with a 1. I can only assume it's because you have effectively done this members homework for them - it doesn't really help anyone.
I would personally have used a CTE instead of another table variable but that is just my personal preference and I don't think the downvote was deserved so have my upvote
Rate this:
Please Sign up or sign in to vote.

Solution 3

We can retrieve the latest created record for each id from a table using the following queries :

Selecting maximum Datetime and get the id in descending order if the id is auto increment :

SELECT * 
FROM table
WHERE Id IN (SELECT Id FROM table WHERE Datetime = (SELECT MAX(Datetime) FROM table))
ORDER BY Id DESC
LIMIT 1
   
v2
Comments
CHill60 25-Jun-20 4:19am
   
This will only retrieve records where the datetime for an Id matches the maximum datetime used in the entire table. It will also only return a single record. The OP clearly stated "i want to retrieve the latest created record for each Id"
Also - whether the id is an auto-increment or not is totally irrelevant

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100