0

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..

Edited by Abemanden: n/a

3
Contributors
15
Replies
16
Views
7 Years
Discussion Span
Last Post by Abemanden
0

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

0

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

0

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.

0

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...

0

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')
)
0

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.

Edited by Abemanden: n/a

0

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
0

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

0

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

0

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

0

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
0

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)
0

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
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.