CODEWITHSUNDEEP
sqlsql-server
luckasx
luckasx

Reputation: 369

SQL Select list of last records with condition

I have the following table

ID Name CodSituation
1 John 1
2 Mary 2
3 Mary 3
4 Mary 4
5 John 5
6 John 2
7 Mary 1

I want to select the Names, ID's and CodSituation for all users where their last entry is CodSituation=2

In these results I will get just the id 6 As Mary's last entry was CodeSituation=4 if more than one users have their latest CodSituation=2 I want them too.

Upvotes: 2

Views: 6417

Answers (4)

Sijin John
Sijin John

Reputation: 579

Put condition using "WHERE" clause and if you want last two records then use ORDER BY "ID" DESC LIMIT 2

Upvotes: 0

Jonathan
Jonathan

Reputation: 1564

[FINAL EDIT] After seeing what was posted at the end of this answer I figured out that the user was asking the wrong question:

What they were asking was 'show me everyone who has CodSituation=2' when they meant 'Show me all the users who's last entry in CodSituation field=2'

Here is the correct query for that:

select a.ID, a.Name, a.CodSituation
from table_name a
inner join (
    select Name, max(ID) as MaxID
    from table_name
    group by Name
) b on a.Name = b.Name and a.ID = b.MaxID 
where a.CodSituation = 2;

Here is the fiddle for that: http://sqlfiddle.com/#!2/a731d [END]

[here are the previous queries, for reference] Looks to me like you just need:

select * from table_name where CodSituation=2

To get all of the people with situation 2

To get only the last entry using mysql:

select * from table_name where CodSituation=2 order by id desc limit 1

To get the last entry using sql-server:

select top 1 * from table_name where CodSituation=2 order by ID desc;

See a working example here:

http://sqlfiddle.com/#!6/022fb/4

[edit]

OP supplied an actual dataset:

select Name from table_name where CodSituation=2 group by Name;

This shows all the unique users with a CodSituation of 2 (with one entry per person)

http://sqlfiddle.com/#!3/be404/2

Upvotes: 8

luckasx
luckasx

Reputation: 369

I found an easier way concatenating fieds and using max solve this problem... Thanks!

SELECT
right(
max(
right(('0000000' + CONVERT(varchar,id) + '-'+ convert(varchar,codsituation)),4)),10),
name
FROM TABLE_NAME
GROUP BY name
HAVING 
right(
max(
right(

  ('0000000' + CONVERT(varchar,id) + '-'+ convert(varchar,codsituation))
  ,4)),1) = 2

http://sqlfiddle.com/#!3/3dc1c/10

Upvotes: 2

huMpty duMpty
huMpty duMpty

Reputation: 14460

Can be achieved like this, but may not be the best way to do this

Basically what I am doing is Create a temporary table and add the Name and Maximum row number.

Which then match again that the maximum row number row is associated with CodSituation=2

 Create Table #Temp2(Name Varchar(10),RowN int)

;WITH CTE AS (SELECT *,RN=ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ID) 
             FROM TableName)

insert into #Temp2         
SELECT Name,MAX(RN)
FROM CTE
Group By Name

select TT.*
from 
(
    SELECT *,RN=ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ID) 
             FROM TableName
)TT
cross apply (
            select Name
            from #Temp2 TB
            where TB.Name=TT.Name and TB.RowN= TT.RN
            ) Tab
Where CodSituation=2

Fiddle Sample

Upvotes: 2

Related Questions