How to Select Lastest Records?

Question

Abemanden 0 Newbie Poster

13 Years Ago

Hi, i've tried several times, but haven't succed in making this:

Classroom2

Name: | Date:
Oliver | 12-10-10
Homer | 12-10-10
Peter | 11-11-10

Classroom1

Name: | Date:
James | 20-10-10
Lars | 20-10-10
John | 11-11-10

How do i make a SQL statement that ONLY selects the lastest dates for each classroom like the example above? (the text in red is that i dont want to select)

I've tried with MAX(Column) function and Group By but none of them seems to work on my web application..

mssql sql

Edited 13 Years Ago by Abemanden because: n/a

3 Contributors
15 Replies
143 Views
1 Week Discussion Span
Latest Post 13 Years Ago Latest Post by Abemanden

All 15 Replies

rch1231 169 Posting Shark

13 Years Ago

By the look of the data your date field does not have the variable type set as a "date". MySQL normally displays date in the format:

DATE 'YYYY-MM-DD'

see the following for more info:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html

rch1231 169 Posting Shark

13 Years Ago

Classroom2
Name: | Date:
Oliver | 2012-10-10
Homer | 2012-10-10
Peter | 2011-11-10
Classroom1
Name: | Date:
James | 2012-10-10
Lars | 2012-10-10
John | 2011-10-10
This my table:
Classroom : Varchar
Date : datetime
Name : varchar
I'm looking for the select statement, not how to format the date...
the text in red i not to be selected

You need a sub query to find the max first then get the data based on the result. If should be something like this:

Select * 
from mytable 
where mytable.classroom = 'Classroom1' 
and mytable.date = (select max(date) 
from mytable where classroom = 'Classroom1')

Watch out for the datetime vs. date variable type if you are looking for all things on the same day.

rch1231 169 Posting Shark

13 Years Ago

thank, now i understand how to use the MAX() properly, but i still have a problem. The SQL statement you gave me will only selects the latest date for one of the "tables". What if i wanted to pick up the latest date for each of the "tables"
Ex:
Classroom2
Name: | Date:
Oliver | 10-10-10
Homer | 08-10-10
Classroom1
Name: | Date:
James | 20-10-10
Lars | 01-10-10
I want both of the greens to be displayed. MAX() will only get the 20-10-10 right? and i will not be able to select the other then...

Picky Picky Picky..

Try this:

(Select * 
from mytable 
where mytable.classroom = 'Classroom1' 
and mytable.date = (select max(date) 
from mytable where classroom = 'Classroom1')
) UNION (
Select * 
from mytable 
where mytable.classroom = 'Classroom2' 
and mytable.date = (select max(date) 
from mytable where classroom = 'Classroom2')
)

rch1231 169 Posting Shark

13 Years Ago

Getting trick now...

Ok here goes...

select mytable.name as Name, crdt.crm as Classroom, crdt.maxdate as `Date`from (select distinct mytable.classroom as crm, max(mytable.date) as maxdate from mytable group by mytable.classroom) as crdt left join mytable where  mytable.classroom = crdt.crm and mytable.date = crdt.maxdate order by mytable.classroom

I use a similar query on a table of mine and it worked...

select Inventory.`Stock Number`, data1.Category, data1.maxpr from (SELECT DISTINCT
Inventory.Category,
Max(Inventory.Price) as maxpr
FROM
Inventory
GROUP BY
Inventory.Category) as data1 left join Inventory on Inventory.Category = data1.Category and Inventory.Price = data1.maxpr
order by data1.Category

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Abemanden 0 Newbie Poster · Answer 1 · 2010-04-23T03:27:24+00:00

the Date this is just an example. Still wondering how to do it.

Abemanden 0 Newbie Poster · Answer 2 · 2010-04-23T03:33:06+00:00

Classroom2

Name: | Date:
Oliver | 2012-10-10
Homer | 2012-10-10
Peter | 2011-11-10

Classroom1

Name: | Date:
James | 2012-10-10
Lars | 2012-10-10
John | 2011-10-10

This my table:
Classroom : Varchar
Date : datetime
Name : varchar

I'm looking for the select statement, not how to format the date...
the text in red i not to be selected

Abemanden 0 Newbie Poster · Answer 3 · 2010-04-23T04:53:01+00:00

You need a sub query to find the max first then get the data based on the result. If should be something like this:
Select * 
from mytable 
where mytable.classroom = 'Classroom1' 
and mytable.date = (select max(date) 
from mytable where classroom = 'Classroom1')
Watch out for the datetime vs. date variable type if you are looking for all things on the same day.

thank, now i understand how to use the MAX() properly, but i still have a problem. The SQL statement you gave me will only selects the latest date for one of the "tables". What if i wanted to pick up the latest date for each of the "tables"

Ex:
Classroom2

Name: | Date:
Oliver | 10-10-10
Homer | 08-10-10

Classroom1

Name: | Date:
James | 20-10-10
Lars | 01-10-10

I want both of the greens to be displayed. MAX() will only get the 20-10-10 right? and i will not be able to select the other then...

Abemanden 0 Newbie Poster · Answer 4 · 2010-04-23T05:41:20+00:00

Picky Picky Picky..

Try this:

(Select * 
from mytable 
where mytable.classroom = 'Classroom1' 
and mytable.date = (select max(date) 
from mytable where classroom = 'Classroom1')
) UNION (
Select * 
from mytable 
where mytable.classroom = 'Classroom2' 
and mytable.date = (select max(date) 
from mytable where classroom = 'Classroom2')
)

Really sorry i have to be like this, but this is not the way if i have infinitive classrooms...

i was thinking to make something like this:

Select * 
From mytable
Where myTable.Date = 
(Select MAX(Date) 
From mytable
Group By Name)

but it gives me an error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Warning: Null value is eliminated by an aggregate or other SET operation.

Abemanden 0 Newbie Poster · Answer 5 · 2010-04-24T02:15:15+00:00

Getting trick now...

Ok here goes...

select mytable.name as Name, crdt.crm as Classroom, crdt.maxdate as `Date`from (select distinct mytable.classroom as crm, max(mytable.date) as maxdate from mytable group by mytable.classroom) as crdt left join mytable where  mytable.classroom = crdt.crm and mytable.date = crdt.maxdate order by mytable.classroom

I use a similar query on a table of mine and it worked...

select Inventory.`Stock Number`, data1.Category, data1.maxpr from (SELECT DISTINCT
Inventory.Category,
Max(Inventory.Price) as maxpr
FROM
Inventory
GROUP BY
Inventory.Category) as data1 left join Inventory on Inventory.Category = data1.Category and Inventory.Price = data1.maxpr
order by data1.Category

Nice, but it gives me a syntax error when i add the thing in red:

select Inventory.`Stock Number`, data1.Category, data1.maxpr from (SELECT DISTINCT
Inventory.Category,
Max(Inventory.Price) as maxpr
FROM
Inventory
GROUP BY
Inventory.Category) as data1 left join Inventory on Inventory.Category = data1.Category and Inventory.Price = data1.maxpr
order by data1.Category

If i dont write (select bla bla bla) before the from, it's ok

Abemanden 0 Newbie Poster · Answer 6 · 2010-04-24T03:15:56+00:00

This is my code. It's a bit messy since i have three tables, but i really dont know how to fix it...

'Classroom' is equal 'a.Name' and c.Date is equal the Date

select a.Name, a.ID, a.Location, a.Fk_Type,
b.Fk_Equipments, b.Type,
c.Fk_MeetRoom, c.Status, c.Note, c.Fk_User, c.Reg_Date
from (Select Distinct a.Name as Name2, MAX(c.Date) as MaxDate
		FROM 
			(MeetRoom as a 
			Full join MeetRoomTypes as b on a.Fk_Type=b.Type 
			Full join EquipmentStatus as c on b.Fk_Equipments=c.Fk_Equipments AND a.ID=c.Fk_MeetRoom
			)
		GROUP By a.Name)			
WHERE a.Location=@Location

I get syntax in the red text.
I also found this website: http://stackoverflow.com/questions/612231/sql-select-rows-with-maxcolumn-value-distinct-by-another-column
which does the same i think, but that didn't work for me neither

Abemanden 0 Newbie Poster · Answer 7 · 2010-04-24T19:48:41+00:00

I got it to work with this code:

select roomNdato.mrn as Name, a.ID, a.Location, a.Fk_Type,
b.Fk_Equipments, b.Type,
c.Fk_MeetRoom, c.Status, c.Note, c.Fk_User, c.Reg_Date, roomNdato.MaxDate AS Date
FROM (MeetRoom as a 
Full join MeetRoomTypes as b on a.Fk_Type=b.Type 
Full join EquipmentStatus as c on b.Fk_Equipments=c.Fk_Equipments AND a.ID=c.Fk_MeetRoom) 

Left join (Select Distinct a.Name AS mrn, MAX(c.Date) as MaxDate From 
(MeetRoom as a 
Full join MeetRoomTypes as b on a.Fk_Type=b.Type 
Full join EquipmentStatus as c on b.Fk_Equipments=c.Fk_Equipments AND a.ID=c.Fk_MeetRoom
) group by a.Name) as roomNdato
on 
a.Name = roomNdato.mrn And
c.Date = roomNdato.MaxDate
Where Location=@Location
order by Name, Date Desc

Now i got a new error, but it's not the sql statement:
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

This must be another subject. Thanks for your help

rch1231 169 Posting Shark · Answer 8 · 2010-04-25T15:56:03+00:00

Nice, but it gives me a syntax error when i add the thing in red:

select Inventory.`Stock Number`, data1.Category, data1.maxpr from (SELECT DISTINCT
Inventory.Category,
Max(Inventory.Price) as maxpr
FROM
Inventory
GROUP BY
Inventory.Category) as data1 left join Inventory on Inventory.Category = data1.Category and Inventory.Price = data1.maxpr
order by data1.Category

If i dont write (select bla bla bla) before the from, it's ok

Hello,

Sorry about that I may not have stated my last response correctly. In the last message the first block of code was for you the second block was the code I used with my tables to test the syntax. You should have used this:

select 
mytable.name as Name, 
crdt.crm as Classroom, 
crdt.maxdate as `Date`
from (select distinct mytable.classroom as crm, 
max(mytable.date) as maxdate 
from mytable 
group by mytable.classroom) as crdt 
left join mytable 
where  mytable.classroom = crdt.crm 
and mytable.date = crdt.maxdate 
order by mytable.classroom

willsee99 0 Newbie Poster · Answer 9 · 2010-05-04T20:14:31+00:00

It should be reasonably straight forward...

Think the key is getting the max(date) by classroom.

Have a look at the following code (changing "my_table" for your actual table):

Select Classroom, Date, Name 
from my_Table t1
where date = (select max(date) from my_table t2 where t1.classroom = t2.classroom)

willsee99 0 Newbie Poster · Answer 10 · 2010-05-04T20:19:04+00:00

Sorry about that - obviously didnt read the entire thread... Back in my box :)

Abemanden 0 Newbie Poster · Answer 11 · 2010-05-05T19:05:33+00:00

Hi i modified the sql with a where statement in the end, and now it's fully functionable. For people who might want this in the future, here is the sql:

SELECT roomNdato.mrn AS Name, a.ID, a.Location, a.Fk_Type,b.Fk_Equipments, b.Type,c.Fk_MeetRoom, c.STATUS, c.Note, c.Fk_User, c.Reg_Date, roomNdato.MaxDate AS Date
FROM 
(MeetRoom AS a 
Full JOIN MeetRoomTypes AS b ON a.Fk_Type=b.Type 
Full JOIN EquipmentStatus AS c ON b.Fk_Equipments=c.Fk_Equipments AND a.ID=c.Fk_MeetRoom)

LEFT JOIN (SELECT DISTINCT a.Name AS mrn, MAX(c.Date) AS MaxDate 
                   FROM (MeetRoom AS a 
                       Full JOIN MeetRoomTypes AS b ON a.Fk_Type=b.Type
                       Full JOIN EquipmentStatus AS c ON b.Fk_Equipments=c.Fk_Equipments AND a.ID=c.Fk_MeetRoom) 
                    GROUP BY a.Name) AS roomNdato
ON a.Name = roomNdato.mrn
AND c.Date = roomNdato.MaxDate
 
WHERE Location=@Location
AND a.name = crdt.crm
AND c.date = crdt.maxdate

ORDER BY Name, Date DESC

How to Select Lastest Records?

Recommended Answers Collapse Answers

All 15 Replies

Recommended Answers